Getting Forms button caption

[Otto Moehrbach]

ExcelXP & WinXP
My objective is to change the caption of the sole Forms button on the active
sheet when the sheet is selected/activated. My code wouldn't work so I
tried various modifications of the code. The following is an attempt to
just get the caption and it doesn't work either.
The error is "Object doesn't support this property or method." and the
offending line of code is the MsgBox line.
How do I get the button caption when the sheet is activated? Thanks for
your help. Otto

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim shp As Shape
For Each shp In Sh.Shapes
shp.Select
MsgBox shp.Characters.Text
Next shp
End Sub

 

[Norman Jones]

Hi Otto,

Try:

Sub XYZ()
Dim BTn As Button

Set BTn = ActiveSheet.Buttons(1)
BTn.Caption = "XYZ"

End Sub

 

[Jim Cone]

Otto,

or...
shp.TextFrame.Characters.Text

Jim Cone
San Francisco, USA


"Otto Moehrbach"
<[email protected]>
wrote in message
ExcelXP & WinXP
My objective is to change the caption of the sole Forms button on the active
sheet when the sheet is selected/activated. My code wouldn't work so I
tried various modifications of the code. The following is an attempt to
just get the caption and it doesn't work either.
The error is "Object doesn't support this property or method." and the
offending line of code is the MsgBox line.
How do I get the button caption when the sheet is activated? Thanks for
your help.
Otto
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim shp As Shape
For Each shp In Sh.Shapes
shp.Select
MsgBox shp.Characters.Text
Next shp
End Sub

 

[Otto Moehrbach]

Norman, Jim
Thanks for your help. Otto