Excel VBA Buttonbar on sheet

user_7251685 0
Viewed 60

I'm trying to find a ButtonBar solution to add to a sheet in excel (so not in forms, directly on the sheet) While looking I ran into the ActivX controll: ButtonBar Class, that gets added like the code below.

Can anyone tell me how I can add buttons to this control?

Or do you know of any other buttonbar bype controls I could use on an Exel sheet?

    ActiveSheet.OLEObjects.Add(ClassType:="UmOutlookAddin.ButtonBar.1", Link:= _
        False, DisplayAsIcon:=False, Left:=96.75, Top:=15, Width:=214.5, _
        Height:=17.25).Select

You can control the click unsing the code below, but I have not found a way to add new buttons:

Private Sub ButtonBar1_OnClick(ByVal ButtonId As Long)
spreadsheet vba excel
1 Answers

I don't think you can add buttons. I tried changing the label and that crashed Excel:

Sub Test()
    Dim bb As ButtonBar
    
    Set bb = ActiveSheet.OLEObjects(1).Object
    bb.SetButtonLabel PlayButtonId, "Test" 'Boom
End Sub

The ButtonBar seems too unstable and I would not recommend using it.

However, you have other options. For example, on the Developer tab you have the simple Button control: button

You can add multiple buttons and then group them: grouped

You could obviously make them adjacent to mimic a bar:
pressed

As you can see, they even have a 'pressed' animation when you click them (mid button).

If you don't need the animation then you can just add any shape to work as a button. You would add one shape and format it and then make copies and assign a different macro for each (with right click and Assign Macro...). You would then group them when done. For example:
colored

Or, you could just use a custom ribbon tab if you don't necessarily need the buttons in the sheet itself. Here is an example where I showed step by step how to add a custom ribbon but there are many ways of doing it if you search the web. In that example the custom ribbon is not used to display anything but rather is used for it's Init event. But it's easy to replace the xml at step 2f with something like this:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="InitRibbon"> 
<ribbon>    
<tabs>  
<tab id ="TestTabID" Label="Test">  
    <group id="FirstGroupID" Label="First Group">   
        <button id="RefreshData" label="Refresh Data" size="large" imageMso="Refresh" onAction="RibbonCallTool" />  
        <button id="UnloadData" label="Unload Data" size="large" imageMso="RecordsDeleteRecord" onAction="RibbonCallTool" />    
    </group>
</tab>  
</tabs> 
</ribbon>   
</customUI>

in which case you would also have this method in a standard VBA module:

'*******************************************************************************
'Callback ("onAction"). Runs when a control is clicked in the Custom Ribbon tab
'*******************************************************************************
Public Sub RibbonCallTool(ByVal ctrl As IRibbonControl)
    Select Case ctrl.ID
    Case "RefreshData"
        MsgBox "Refresh"
    Case "UnloadData"
        MsgBox "Unload"
    Case Else
        Debug.Print "Control <" & ctrl.ID & "> does not have an associated action attached!"
    End Select
End Sub

Finally, you could always have a single button that opens a modeless form with all the menus you need.

user_84889130
Related