Colored buttons?

Topic

New Reply

Whenever I create a button (e.g. to assign a macro), it is always grey in color. Is it possible to have colored buttons (I know how to color the fonts but not the button itself). Thanks in advance.

Reply | Quote

Replies

You are restricted in the colours you can apply to Command Buttons, Dialog Boxes etc. These colours are tied in to the Desktop Scheme you set up under Windows (Control Panel, Display, Appearance).

However you can add colour to your macros by using Drawing Objects. You can assign macros to these objects, and can use your own choice of colour scheme (and shape, shadow, 3D effects etc).

regards,

Andrew C

Reply | Quote

What version of Excel are you using, where is the button (on the sheet or in a user form), and how did you create the button (the control tool box?)?

On Excel 97, if I create a button on a spread sheet using the Control Tool box (from View/Toolbars), I can right click on the button and select Properties. In the properties window, I can click on BackColor. I then get a window for selecting the color. I can click on the Pallet tab at the top of this window and I have a full selection of colors for the button.

Reply | Quote

Legare

I use both Excel 97 & 2000, but you are right – I never noticed the palette tab. I just select (or rather don’t select) from the system tab. I prefer to stick to system colours, but that’s no reason for others to do the same.

Is the palette restricted or do you have access to all colours?. ( I can see 48, including shades of grey). What happens if the sheet is displayed on a PC set to display only 16 colours (unlikely) ?

Thanks for that enlightennment,

Andrew

Reply | Quote

I don’t really know. However, if the PC were set up to only be able to display 16 colors, that is all that you would be able to see in the pallet, no matter how many it tried to display. If I had to guess, I would guess that it would only try to display 16.

Reply | Quote

Yeah, generally, it selects the color closest to the original when displayed in 16 colors.

Drk.

Reply | Quote

I have Excel 2000/Win98SE. I usually create buttons by opening the forms toolbar and dragging a button to a worksheet. I tried the method that Legare suggested (i.e. by using the Control Toolbox) and by right clicking & selecting Properties, I do indeed see a Back Color option and the palette. So that clarifies that point! But raises another: Is there any difference in the buttons created via a Forms toolbar versus a Control Toolbox toolbar? One glaring difference I notice is that when created from the Control Toolbox, there is no option to assign a macro (or am I missing something?) Since I create buttons primarily to assign macros, it would seem to me that I can only use the Forms toolbar for that, and that leaves me with the situation that I cannot assign colors to that button! Any thoughts?

Reply | Quote

Well, I don’t know what good a button would be if you could not run code from it.

Create a button from the Control ToolBox. Right click on the button, and right under properties is “View Code.” Select that, and you will be taken to a place where you can put your VBA code. If you already have the code in a standard VBA module, you can just call it from the button click event.

Reply | Quote

I am sorry if I seem dense, but I have just started exploring Excel’s many features. I understand what a macro is, but don’t quite understand what a VBA module is, nor do I know how to “call it from the button click event”.
So if I have an existing macro, I first open it and copy its contents to the clipboard; then I create a button from the Control Toolbox -> right click -> open View Code, and then copy the contents of the clipboard into this window and this will assign the macro code to this button – right? Hmm, why didn’t Microsoft let one easily assign a macro instead of going about it in this roundabout way (that is, for buttons created with the Control Toolbox; the Forms toolbar buttons let you easily assign macros)? Or is there another, simpler way – for novices like me! Thanks for your patience.

Reply | Quote

Have you considered adding a button to one of your toolbars to run the macro ?

That is probably Microsoft’s “easy” way of doing it.

If you goto Tools|Customize| and select the Commands Tab, then scroll down through the categories until you find macros, drag the custom button a (yellow smiley) to a toolbar, and if you want change it click modify selection.

Then when you click on your new button for the first time it present you with a list of macros from which you can select then one you want associated with that button.

AC

Reply | Quote

Andrew, I see what you mean (BTW, Modify Selection remains greyed out on selecting the Custom button Tools|Customize|Commands|Macros on my system – Win98SE/Excel2000). And yes, after placing this on a toolbar and clicking on it for the 1st time, it asks for a macro. But once you do that, I see no way of changing it. Also, I like buttons on the sheet, not on the toolbar. And if they all looked the same (note:Modify selection doesn’t work), then it would be hard to figure out which macro was assigned to which! But your suggestion was good – and again I learnt something new!

Reply | Quote

Just for the record, you must place the button on a toolbar in order to modify it. Just drag it to a toolbar and the modify selection should be available. You then get a selection of buttons to choose from, or if you like you can design your own.

It is possible that after dragging the button to the toolbar it became unselected, in which case just click on it and all should be ok.

You can assign a macro to the button by right clicking on it, selecting customize, and when the dialog box opens right click again on the toolbar button and you should have an option to (re)assign a macro.

Hope this helps,

Andrew

Reply | Quote

Andrew, you are right – everything works just like you said.

Reply | Quote

Many people use VBA module/procedure and macro interchangeably. However, for those of us who remember older versions of Excel where macros were created on a macro spreadsheet and used a very different language than VBA, there is a big difference. When I hear macro, I think of the old Excel Macro sheets. So, when I am referring to VBA, I try to refer to it as a VBA procedure (ie. Subroutine or Function).

Yes, you can copy your “macro” code from a VBA procedure and just paste it into the button click event procedure. However, I usually prefer to put the “macro” code in a standard VBA module (what you get when you press Alt/F11 from a spreadsheet window), and call it from the click event procedure. If the “macro” in the standard module is named DoWhatIWant, then the click event procedure would look like this:

Private Sub CommandButton1_Click()
    Call DoWhatIWant()
End Sub

By doing this, I can then use the code from more than one button if I need to.

Reply | Quote

Legare, I am still struggling so I request your patience. Here’s what I did: Created a new button from the control toolbox, assigned it a nice color, and then went into View code. There I typed in Call ClearConstants(), which is a macro I have. When I went back to the sheet and tried clicking on the button, it wouldn’t let me click; the cursor over the mouse becomes 4 arrows pointed north, south, east & west (you know what I mean). I also gave the button a new name (test) and since it wasn’t letting me click I closed the sheet & exited Excel. I then restarted Excel and went into my worksheet -> this time, the button let me click on it but doing so brought up an error box -> I clicked OK and it went into Visual Basic Editor (? or something like that) and highlighted the first line in yellow which read Private Sub CommandButton1_Click(); since I had renamed the button, I modified that line to
Private Sub test_Click()
and exited the VBA editor (or whatever it was). Now, when I click the button, nothing happens – neither the error box appears nor does the macro execute. One other thing -> I can’t move the button to another place on the sheet, nor can I edit the View code (no menu pops up when I right click the button). Is all this a consequence of renaming the button? How do I delete the button from my worksheet, or modify its properties (color, code, etc.)
Also, in your post you said “I can then use the code from more than one button if I need to” – does that mean you can assign more than 1 macro to a button?
Please understand I am new to all this so what you take for granted may not be comprehensible to me! (hey, my name is Stumped – and while that’s not entirely wrong, maybe it should have been Stupid!!)

Reply | Quote

Your macro will run after you click on the button in the upper left-hand corner of the control toolbox to “Exit Design Mode”.

Reply | Quote

You need to exit from design mode. Press Alt+F11 to get into the VBA Editor. On the Run menu select “Exit design mode.” Now, when you go back to the sheet, the cursor should change to an arrow when over the button and you should be able to press it.

Reply | Quote

I am definitely *not* in the design mode. Yet on pressing the button, the macro doesn’t execute. Also, on right clicking it, no menu pops up, so I am unable to modify its color, name or code; I can’t move or delete the button, either.

Reply | Quote

OK, to get back into design mode, go to the View menu Toolbars and display the Controls Toolbox. In the top left corner of the toolbox should be an Icon that looks like a draftsman’s triangle, ruler, and pencil. If you hover the mouse pointer over this Icon, it should say “Design Mode.” Click on this Icon to get back into design mode. Once back in design mode, you should be able to move the button and get into the code and properties. You will, of course, have to click on the “Design Mode” button again before you can click on the button to rin the code.

What code do you have in the button click event routine, and how do you know it is not executing?

Reply | Quote

I was able to get into the design mode, as you suggested, and was able to move the button around, view code, etc. Then I exited it. The folloeing code exists (note – I had renamed the button to ‘test’):

Private Sub test_Click()
ClearConstants
End Sub

Private Sub CommandButton1_Click()
ClearConstants
End Sub

I then changed to this, thinking maybe the reference to the macro wasn’t proper:

Private Sub test_Click()
Call Personal.xls!ClearConstants
End Sub

Private Sub CommandButton1_Click()
Call Personal.xls!ClearConstants
End Sub

The macro still doesn’t run (I know because its supposed to prompt me for the column to clear).
Thinking maybe the button itself was defective, I created another, and now I have:

Private Sub test_Click()
Call Personal.xls!ClearConstants
End Sub

Private Sub CommandButton1_Click()
Call Personal.xls!ClearConstants
End Sub

Private Sub CommandButton2_Click()
Call Personal.xls!ClearConstants
End Sub

The macro still doesn’t run from either button. All I get by pressing either button is a message saying syntax or compile error, and when I click OK it takes me to the VBA code and the first line of the respective button gets highlighted in yellow. I am stumped! (Out of curiosity, what does the word Private in the first line of the code mean?)

Reply | Quote

It appears that if the macro is in a different workbook, you have to use the Run method rather than the call statement. Change the Call statement in the button click event to:

    Run "Personal.xls!ClearConstants"

Reply | Quote

I changed to run but still got an error message (a different one this time: Run time error 424; Object required. ??Huh??).
Then I just copied the “body” of the macro code (not including the first line, which includes its name ClearConstants) and the result now is:

Private Sub CommandButton1_Click()
Dim strColumn As String
strColumn = InputBox(“Enter column to clear.”)
If strColumn = “” Then Exit Sub
Range(strColumn & “15:” & strColumn & “48”).SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

I am happy to say that now its working! So I suppose the lesson is that one needs to input the macro code (atleast on my system).
1) I am still curious to know what the word Private (or Public) means in a macro
2) Even though my button is named test, the macro code as you will see is under CommandButton1 (this was the first button I created with the control toolbox), so I guess renaming the button has no effect on the VBA code?
3) Now whenever I open my spreadsheet with the control toolbox created button, I am prompted to enable/disable macros every time which is irritating; prior to creating this button, there were tens of macros in the sheet and I never got prompted, but now I do; is there a workaround?

Reply | Quote

Since you did not show me the code that you used for the Run method, it is difficult to say why it did not work. Did you have the name of the macro to run in double quotes like I showed in my example?

Public and Private determine the scope of the procedure. A Private procedure can only be “seen” by other procedures in the same module. A Private Sub procedure does not show up on the Tools Macro menu, and can not be called by procedures in other modules or workbooks. Public makes the procedure available to everything.

Buttons have names and captions. If you change the caption, that will change the label that displays on the button, but does not change the name that you use in VBA code to refer to the button. If you change the button name, then the name you use in VBA code has to change, but the label on the button does not change.

I there are any macros in a workbook, and if macro virus protection is enabled, then Excel should always ask if the macros should be enabled. That is to protect you from viruses that can be in Excel workbooks. Were those “tens of macros” in the workbook that you were opening, or are they in your Personal.xls?” I don’t think Excel prompts to enable macros if they are in Personal.xls and Personal.xls is in your xlstart directory. You can turn off the prompt to enable macros by going to Tools/Options, click on the General tab and deselect “Macro virus protection.” However, I do NOT recommend doing that. In Excel 2000, you can also add a digital signature to the workbook and tell Excel to trust that digital signature, and the prompt will not appear.

Reply | Quote

Legare, as usual your answer is detailed and enlightening.
1) No, I did not have the macro name in double quotes. I tested that with another button and it works OK. So the Run command & double quotes & explicit macro name (including the personal.xls name) does the trick.
2) All of my macros are in personal.xls (the hidden workbook) – I guess whenever I create them they just land up there.
3) Just another question…in an earlier post, you had said “I can then use the code from more than one button if I need to” – does that mean you can assign more than 1 macro to a button?

Reply | Quote

You can Call or Run as many macros from the button click event as you want to. The click event procedure is “assigned” to the button (in your terminology), but that is just another procedire (macro), and can call anything it wants.

Reply | Quote

Just curious…if you assign > 1 macro, are they run alphabetically by their names or in the order in which they are entered in the code?
I noticed 2 things just now:
a) When I copied my previously created “test” button from one sheet of the workbook to another, I had to re-enter the code – I guess the button gets copied but the code does not?
I gave the button a rather long name but the text doesn’t wrap around to a second line (I tried the Enter and Alt-Enter keys to break the text, but no luck) with the result only a portion of the text is visible on the button. Any workaround for this?

Reply | Quote

The code that you place in the event procedure is executed just like any other code. So, if you don’t have any code like If statements, or GoTo statements that alter the order of execution, then the procedures you call from the event procedure will be called in the order the call statements appear. You are in complete control of the order.

You are correct, when you copy and paste a button, the event procedures for the button are not copied. You do have to recreate those.

When you say you gave a button a rather long “name”, do you really mean caption as the rest of the question implies. You are talking about the text label that displays on the face of the button? If you are talking about the Caption, then, at the bottom of the button properties, there is a property named “WorrdWrap.” Set this property to true, and the caption text should wrap to multiple lines.

Reply | Quote

– I have not tested the button with multiple macros but I am sure it will work as you say it should
– I got the word wrap to function after implementing your suggestion
– Thanks a lot, Legare, and everyone else, for posting your responses. I finally know how to create colored buttons and assign macro(s) to them!
(I have run into a new and completely unrelated problem in Excel now and I will try to create a new post on that later today or tomorrow).

Reply | Quote

If you do as Legare suggests re the Code, you can select properties as well and apply your colour, system or palette.

I have a suspicion that if you use either of these colouring methods it could appear very differently an another PC if the palette or system colours are set differently.

Regards,

Andrew C

Reply | Quote