Change caption of toggle button

smirnoff103 0
Viewed 33

I have two toggle buttons, each on a different sheet. The code of the toggle button is stored on the sheet level:

Private Sub MoveButton_Click()
    ClickToggleButton "move", ActiveSheet.Name
    'ToggleButtonPressed sSHEET2NAME
End Sub

The ClickToggleButton function is stored on the module level (because I want to use the same function for both buttons).

In my case while clicking one toggle button, the other button on the other sheet must be pressed, this works out well:

'switch button state to the same over the 2 sheets
Select Case sheetName
    Case sSHEET1NAME
        Worksheets(sSHEET2NAME).OLEObjects(sBUTTONMOVENAME).Object.Value = Worksheets(sSHEET1NAME).OLEObjects(sBUTTONMOVENAME).Object.Value
    Case sSHEET2NAME
        Worksheets(sSHEET1NAME).OLEObjects(sBUTTONMOVENAME).Object.Value = Worksheets(sSHEET2NAME).OLEObjects(sBUTTONMOVENAME).Object.Value
End Select

Now I only need to change the text (caption) on both buttons depending on the value you are in, I got error 438 "Object doesn't support this property or method" on this line:

 Worksheets(ActiveSheet.Name).OLEObjects(sBUTTONMOVENAME).Caption = sButtonDescription

I guess it has to do with the fact where my code was stored ? I can change the value of the object but can't change the caption.

Thanks for your help.

vba excel
1 Answers

You set the caption of the button like you set the value - you need to use the Object-property of the OLEObject.

The OLEObject is just a wrapper around different kind of objects, while the Object-property is the button (or checkbox, or textbox or...) itself:

ActiveSheet.OLEObjects(sBUTTONMOVENAME).Object.Caption = sButtonDescription

You can also set other properties like Font, Color and the like:

With ActiveSheet.OLEObjects(sBUTTONMOVENAME).Objectws.OLEObjects(1).Object
    .Font.Size = 14
    .FontBold = True
    .FontName = "Broadway"
    .ForeColor = vbRed
    .BackColor = vbYellow
End With
FunThomas0
Related