custom toolbar button that visually toggles in and out
 

Excel has a few toolbar buttons that are toggle buttons. For example
- the Toggle Grid button on the Forms toolbar or the Design Mode
button on the Control toolbar. The visual appearance of these buttons
changes as you toggle them "in and out".

I know how to create a custom toolbar button that toggles code that's
assigned to it (for example: ActiveCell.Font.Bold = Not
ActiveCell.Font.Bold) but the visual appearance of such a custom
toolbar button doesn't change, so the user cannot tell which state
it's in. I've even tried assigning my macro to the Toggle Grid button
but that somehow disengaged its ability to change appearance as you
toggled it.

Is there any code that can cause a custom toolbar button to visually
appear to toggle "in and out" with successive clicks?

If that's not possible, is there any code that can change the
foreground color of the button as you "toggle" it? For example, if I
edited the button's image by drawing a solid square on it, is it
possible to have the color of the square change back and forth between
red and black as you "toggle" the button?

Many thanks,
Paul

custom toolbar button that visually toggles in and out
 

Rob,

TERRIFIC!! It works perfectly and is exactly what I was looking for!

Thank you so very much for you time and help - I really appreciate it.

Best regards,
Paul



"Rob Bovey" wrote in message ...
Hi Paul,

ctlButton.State = msoButtonDown
ctlButton.State = msoButtonUp

where ctlButton is a reference to your CommandBarButton will toggle the
button state between in and out.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Paul Simon" wrote in message
om...
Excel has a few toolbar buttons that are toggle buttons. For example
- the Toggle Grid button on the Forms toolbar or the Design Mode
button on the Control toolbar. The visual appearance of these buttons
changes as you toggle them "in and out".

I know how to create a custom toolbar button that toggles code that's
assigned to it (for example: ActiveCell.Font.Bold = Not
ActiveCell.Font.Bold) but the visual appearance of such a custom
toolbar button doesn't change, so the user cannot tell which state
it's in. I've even tried assigning my macro to the Toggle Grid button
but that somehow disengaged its ability to change appearance as you
toggled it.

Is there any code that can cause a custom toolbar button to visually
appear to toggle "in and out" with successive clicks?

If that's not possible, is there any code that can change the
foreground color of the button as you "toggle" it? For example, if I
edited the button's image by drawing a solid square on it, is it
possible to have the color of the square change back and forth between
red and black as you "toggle" the button?

Many thanks,
Paul