PDA

View Full Version : Solved: Change caption of a button on spreadsheet without selecting it



Benzadeus
08-18-2009, 04:31 AM
"btnMostrar" is a button I created on my spreadsheet.

I want to change by VBA its caption when activating its worksheet. I'm using the code

Private Sub Worksheet_Activate()
ActiveSheet.Shapes("btnMostrar").Select
Selection.Characters.Text = "Mostrar Treinamentos"
End Sub

but I'd like something like
Private Sub Worksheet_Activate()
ActiveSheet.Shapes("btnMostrar").Characters.Text = "Mostrar Treinamentos" 'this doesn't work
End Sub

because I don't want to deselect the last selected cell on the spreasheet. Yes, I know I could use something like
Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Selection
ActiveSheet.Shapes("btnMostrar").Select
Selection.Characters.Text = "Mostrar Treinamentos"
rng.Select
Set rng = Nothing
End Sub

but I'd like to change the name directly without selecting the button. Any ideas?

GTO
08-18-2009, 04:41 AM
Hi Benzadeus,

Presuming its an activex command button, try:

Private Sub Worksheet_Activate()
Me.OLEObjects("btnOne").Object.Caption = "SomethingElse"
End Sub


Hope that helps,

Mark

Benzadeus
08-18-2009, 12:37 PM
It didn't work.

Bob Phillips
08-18-2009, 12:58 PM
Private Sub Worksheet_Activate()
ActiveSheet.Shapes("btnMostrar").TextFrame.Characters.Text = "Mostrar Treinamentos"
End Sub

Benzadeus
08-19-2009, 07:55 AM
It worked. Thank you xld and GTO.