Edit text in a Command button

[tkincaid]

I used commandbutton from the control toolbar toolbox, I want to edit the
text on the button, the problem is I have over 500 sheets to do this to, can
I run a macro to complete this task.

 

[Ron de Bruin]

Hi tkincaid

500 Worksheets or workbooks ?

 

[Mike H]

Hi,

Is there only one button on each of these 500 sheets? If there are more than
one how do we know which button to change the caption on?

Mike

 

[Rick Rothstein]

You didn't tell us how you wanted to change the text, but here is some code
that should get you started...

Sub ChangeCommandButtonCaption()
Dim Obj As Object
Dim WS As Worksheet
For Each WS In Worksheets
For Each Obj In WS.OLEObjects
If TypeOf Obj.Object Is CommandButton Then
Obj.Object.Caption = Obj.Object.Caption & " - 123"
End If
Next
Next
End Sub

The code in the If..Then block simply concatenates " - 123" on to the end
every CommandButton caption in the active workbook; obviously you would
change the code in the If..Then block to do whatever it is you wanted it to
do.

 

[tkincaid]

Hi Guys,

Thanks for responding back. There are 500 worksheets in this workbook and
only two buttons on each sheet. However the contents on the spreadsheet is
changing and a quicker way to update this information is needed. I
appreciate your help.

 

[tkincaid]

Hello Rick,

I tried your code and it didn't work for me. Here is my code that I need to
insert into a loop to make this happen on all of the 500+ worksheets:

'Instruction to change every button (identified as Button 3) in the workbook

ActiveSheet.Shapes("Button 3").Select
Selection.Characters.Text = "Another Facility"
With Selection.Characters(Start:=1, Length:=16).Font
..Name = "Arial"
..FontStyle = "Bold"
..Size = 12
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = x1UnderlineStyleNone
..ColorIndex = 5
End With

I hope this helps, thanks.

 

[Rick Rothstein]

You said in your original message that your CommandButtons came from the
Control Toolbar Toolbox... if that is truly the case, then the default names
for the buttons would not have a space in them (that is, your button would
**not** be named "Button 3") and you would not be using the Shapes
collection to control them (as the code you posted indicates). The
CommandButtons from the Control Toolbar and the Buttons from the Forms
toolbar (where your button name indicates it comes from) are two completely
different entities and the method of controlling them through code is
completely different as well. So... which toolbar did you get the buttons
from (and, as an aside, did all the buttons come from the same toolbar)?

 

[tkincaid]

Thanks for all your help

You said in your original message that your CommandButtons came from the
Control Toolbar Toolbox... if that is truly the case, then the default names
for the buttons would not have a space in them (that is, your button would
**not** be named "Button 3") and you would not be using the Shapes
collection to control them (as the code you posted indicates). The
CommandButtons from the Control Toolbar and the Buttons from the Forms
toolbar (where your button name indicates it comes from) are two completely
different entities and the method of controlling them through code is
completely different as well. So... which toolbar did you get the buttons
from (and, as an aside, did all the buttons come from the same toolbar)?

 

[Rick Rothstein]

I was kind of expecting a different response. Is your problem solved?