PDA

View Full Version : How Do I Get Commandbar Values?



Doctor P
04-24-2006, 04:48 AM
I am creating a custom toolbar and want to add some of the default excel commandbarbuttons. I have added the "Font Colour" toolbar button. This button is a msoControlSplitButtonPopup type.

The question is, if I attach a macro to this button, how do I determine which value (or colour) was selected?

I would like to do something similar with the "Line Style" and "Line Colour" toolbar buttons.

TonyJollans
04-24-2006, 05:26 AM
If you attach a macro to the button, then surely the built-in action won't run so there won't be any selection to be made.

Jan Karel Pieterse
04-24-2006, 10:32 AM
You cannot attach a macro to that type of control.

Doctor P
04-25-2006, 05:55 AM
Perhaps there is a workaround solution. Is it possible to retrieve the current value/selection from a font button, or fill button. ie how can I retrieve the current colour selection showing on the face of the button?

Jan Karel Pieterse
04-25-2006, 07:00 AM
Well, you cannot access the button's face easily, but the tooltip does give what color is selected:

MsgBox Application.CommandBars("formatting").FindControl(ID:=401).TooltipText

But that gives you no clue what color index that is in the workbook...
How about telling us what you want to achieve exactly?

Doctor P
04-27-2006, 08:03 PM
I am developing a toolbar for formatting broders, and want to use the "Line style" toolbar drop-down as a method of changing all the borders of a selection to that line style. I would also like to use the "line colour" toolbar button to do the same thing. I can then use those two excel menus in the toolbar and then have an "apply" button to read the selections and apply the appropriate style to the cell selection. This would be much nicer than having an ugly custom menu.

geekgirlau
04-27-2006, 10:15 PM
Why don't you just add the default button for those options to your toolbar?


' add buttons
With Application.CommandBars("mnuTest").Controls
.Add Type:=msoControlSplitButtonPopup, ID:=401, Before:=1
.Add Type:=msoControlButtonPopup, ID:=692, Before:=2
.Add Type:=msoControlSplitButtonPopup, ID:=1692, Before:=3
End With

Doctor P
05-01-2006, 09:22 PM
I think I might just give up on this one. Adding the default options is fine, but I might as well just copy the original control.

What I want is to read the type of line showing on the commandbarbutton, or read the currently selected colour on the commandbarbutton.