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.

Hi tkincaid

500 Worksheets or workbooks ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"tkincaid" wrote in message ...
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.

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

"tkincaid" wrote:

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.

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.

"Mike H" wrote:

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

"tkincaid" wrote:

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.

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.

--
Rick (MVP - Excel)


"tkincaid" wrote in message
...
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.

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.

"tkincaid" wrote:

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.

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 (MVP - Excel)


"tkincaid" wrote in message
...
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.

"tkincaid" wrote:

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.

Thanks for all your help

"Rick Rothstein" wrote:

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 (MVP - Excel)


"tkincaid" wrote in message
...
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.

"tkincaid" wrote:

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.

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

--
Rick (MVP - Excel)


"tkincaid" wrote in message
...
Thanks for all your help

"Rick Rothstein" wrote:

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 (MVP - Excel)


"tkincaid" wrote in message
...
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.

"tkincaid" wrote:

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.