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.
CodePudding user response:
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