Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Use a command button and mouse click to change cell value

0

I have a userform with a bunch of symbols on command buttons.  These symbols use a specific font and lowercase letters.

I need to select the command button with the symbol I need to use and using a mouse click on a cell place the lowercase letter and format the cell with the font.

I actually have it working using a prompt, but its extremely clunky and the technicians using the workbook don't like it....  Neither do I!

Can this function be done differently?  See my code below:

Private Sub cb_CHAMFER_Click()
    Dim rng As Range
    Set rng = Application.InputBox(prompt:="Select Destination", Type:=8)
    ActiveCell.Font.Name = "GDT"
    ActiveCell.Value = "w"
End Sub
Answer
Discuss

Discussion

I wonder if you could do this better using a Gallery in the context menu. Depends upon how many symbols you have. Please take a look at this code. It's written for MS Word and it creates fewer buttons than a Gallery might contain, however, you can try out the principle: 3 clicks (right - open gallery - select symbol) with immediate actions. The above code inserts a comment. Your version would insert the symbol in the ActiveCell.
Variatus (rep: 4889) Aug 2, '17 at 8:07 pm
An alternative to the above would be to use the double-click event to call your userform and make the form close when the user clicks the appropriate command button (perhaps the command button isn't the best control to use, a lable might do a better job). In this way you would need only two clicks. The selected symbol would be written to the douple-clicked cell.
Variatus (rep: 4889) Aug 2, '17 at 8:50 pm
If neither of the above suggestions seems feasible you could use either the context menu or a double-click event to toggle the font in the ActiveCell as you have suggested.
Variatus (rep: 4889) Aug 2, '17 at 8:53 pm
Add to Discussion

Answers

0

I'm not visualizing what you're trying to do, however, Perhaps using Conditional Formatting may work? (perhaps copy the symbol to Column A and use Conditional formatting to change the font of adjacent cells (B ~ F).

Or have a 'Table' of fonts and make macro to COPY FORMATs of the Adjacent cell (with the fonts you'd like).

Discuss

Discussion

It is kind of tough to explain.  But the excel file is for making reports and can have 1 to hundrads of reports in it that automatically generate based on the users requirements.  One of the functions allows the user to insert symbols that represent certain requirements needed in the report (GD&T Symbols) each report will be different.  So I made a userform that allows the user to select the symbol to insert and then select the cell where is goes, instead of having to remember what letter corrisponds to what symbol.  It works as is, if you have the GDT font you can test is by putting the supplied code in a command button.  Currently you select a cell, select the button and have to select the cell once more before the letter and format are applied.  I'd like to use something like a toggle button to turn on the function, let the operator click through the reports and when their done with that symbol untoggle and turn the function off.  Formatting directly in the workbook and maybe saving as a template or something is not an option that I would choose as basically excel is a blank when you start and I have created an interface that creates everything the operator needs to do there job.  I did this so that functions and operations required cannot be changed without modifying my code, which I have a checks and balances written into so that I know if the operator is messing around and does change something.
Mich_Robert Aug 2, '17 at 2:40 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login