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

Hi Otto,

Try:

Sub XYZ()
Dim BTn As Button

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

End Sub



---
Regards,
Norman



"Otto Moehrbach" 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,

or...
shp.TextFrame.Characters.Text

Jim Cone
San Francisco, USA


"Otto Moehrbach"

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

Norman, Jim
Thanks for your help. Otto
"Otto Moehrbach" 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