Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

Chip & Emory,

Very tentitively - following seems to work for buttons from the Forms menu:

Sub test()
Dim bts As Buttons

'ActiveSheet.Buttons.Font.ColorIndex = 3

Set bts = ActiveSheet.Buttons
bts.OnAction = "MyMacro"
bts.Copy
Worksheets("Sheet2").Paste
Application.CutCopyMode = False
bts.Delete
End Sub

Sub MyMacro()
MsgBox Application.Caller
End Sub

Regards,
Peter T

"Chip Pearson" wrote in message
...
No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

Chip was speaking of controls from the control toolbox toolbar.

The buttons collection for buttons from the forms toolbar does have a delete
method

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Chip & Emory,

Very tentitively - following seems to work for buttons from the Forms
menu:

Sub test()
Dim bts As Buttons

'ActiveSheet.Buttons.Font.ColorIndex = 3

Set bts = ActiveSheet.Buttons
bts.OnAction = "MyMacro"
bts.Copy
Worksheets("Sheet2").Paste
Application.CutCopyMode = False
bts.Delete
End Sub

Sub MyMacro()
MsgBox Application.Caller
End Sub

Regards,
Peter T

"Chip Pearson" wrote in message
...
No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

Emory,

You can try the following code, but it will select all Toolbox Controls on
your sheet.

Regards,
KL

Sub SelectAllControlToolboxShapes()
With ActiveSheet.Shapes
numShapes = .Count
If numShapes 1 Then
ReDim btnsArray(0)
For i = 1 To numShapes
If .Item(i).Type = 12 Then
btnsArray(UBound(btnsArray)) = .Item(i).Name
ReDim Preserve btnsArray(UBound(btnsArray) + 1)
End If
Next
If UBound(btnsArray) 0 Then
ReDim Preserve btnsArray(UBound(btnsArray) - 1)
.Range(btnsArray).Select
End If
End If
End With
End Sub

"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

This one seems to select CommandButtons only.
Regards,
KL

Sub SelectActiveXButtons()
Dim OLEobj As Excel.OLEObject

ReDim btnsArray(0)
For Each OLEobj In ActiveSheet.OLEObjects
If TypeOf OLEobj.Object Is msforms.CommandButton Then
btnsArray(UBound(btnsArray)) = OLEobj.Name
ReDim Preserve btnsArray(UBound(btnsArray) + 1)
End If
Next OLEobj

If UBound(btnsArray) 0 Then
ReDim Preserve btnsArray(UBound(btnsArray) - 1)
ActiveSheet.Shapes.Range(btnsArray).Select
End If
End Sub


"KL" wrote in message
...
Emory,

You can try the following code, but it will select all Toolbox Controls on
your sheet.

Regards,
KL

Sub SelectAllControlToolboxShapes()
With ActiveSheet.Shapes
numShapes = .Count
If numShapes 1 Then
ReDim btnsArray(0)
For i = 1 To numShapes
If .Item(i).Type = 12 Then
btnsArray(UBound(btnsArray)) = .Item(i).Name
ReDim Preserve btnsArray(UBound(btnsArray) + 1)
End If
Next
If UBound(btnsArray) 0 Then
ReDim Preserve btnsArray(UBound(btnsArray) - 1)
.Range(btnsArray).Select
End If
End If
End With
End Sub

"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

Chip was speaking of controls from the control toolbox toolbar.

Yes I realize.

The OP did not specify what sort of buttons, so I thought 60/40 he might
have wanted to reference all Forms' buttons in a statement, as he put it.

I suppose were his buttons Controls, he could also reference all OLEObjects
in one go:

Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
ActiveSheet.OLEObjects.Delete

Of course not specific to CommandButtons, limited and could include
something unintended.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

The buttons collection for buttons from the forms toolbar does have a
delete
method

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Chip & Emory,

Very tentitively - following seems to work for buttons from the Forms
menu:

Sub test()
Dim bts As Buttons

'ActiveSheet.Buttons.Font.ColorIndex = 3

Set bts = ActiveSheet.Buttons
bts.OnAction = "MyMacro"
bts.Copy
Worksheets("Sheet2").Paste
Application.CutCopyMode = False
bts.Delete
End Sub

Sub MyMacro()
MsgBox Application.Caller
End Sub

Regards,
Peter T

"Chip Pearson" wrote in message
...
No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

In article ,
says...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory


Chip, Peter, Tom and KL

The buttons are macro buttons from the forms toolbar.
There are a few hundred on the worksheet, I think.

I plugged each suggested code
into the workbook
as a macro
but none seem to do anything on this paticular sheet.
So, for now I will work by hand.

If anyone wants the workbook to play with:

[remove EMORY]

Thank you all for your responses and your efforts.

Emory

Emory,

Have you tried this?

ActiveSheet.Buttons.Select

or

ActiveSheet.Buttons.Delete

This should work on Forms Toolbar Buttons.

Regards,
KL

"Emory Richter" wrote in message
om...
In article ,
says...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory


Chip, Peter, Tom and KL

The buttons are macro buttons from the forms toolbar.
There are a few hundred on the worksheet, I think.

I plugged each suggested code
into the workbook
as a macro
but none seem to do anything on this paticular sheet.
So, for now I will work by hand.

If anyone wants the workbook to play with:

[remove EMORY]

Thank you all for your responses and your efforts.

Emory

I think you meant
Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
select.Delete

Otherwise, not sure why you introduced oOLEs

Also, in your original response, you said "Very tentitively" as if you were
contradicting Chip. I just clarified for the OP that Chip was speaking of
commandbuttons and not buttons from the Forms toolbar. Shame on the OP for
not being more specific <g.

--
Regards,
Tom Ogilvy



"Peter T" <peter_t@discussions wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

Yes I realize.

The OP did not specify what sort of buttons, so I thought 60/40 he might
have wanted to reference all Forms' buttons in a statement, as he put it.

I suppose were his buttons Controls, he could also reference all
OLEObjects
in one go:

Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
ActiveSheet.OLEObjects.Delete

Of course not specific to CommandButtons, limited and could include
something unintended.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

The buttons collection for buttons from the forms toolbar does have a
delete
method

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Chip & Emory,

Very tentitively - following seems to work for buttons from the Forms
menu:

Sub test()
Dim bts As Buttons

'ActiveSheet.Buttons.Font.ColorIndex = 3

Set bts = ActiveSheet.Buttons
bts.OnAction = "MyMacro"
bts.Copy
Worksheets("Sheet2").Paste
Application.CutCopyMode = False
bts.Delete
End Sub

Sub MyMacro()
MsgBox Application.Caller
End Sub

Regards,
Peter T

"Chip Pearson" wrote in message
...
No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

worksheets("Sheet1").Buttons.Delete

or
worksheets("Sheet1").Select
Worksheets("Sheet1").Buttons.Select
Selection.Delete

for buttons from the forms toolbar located on Sheet1.

--
Regards,
Tom Ogilvy






"KL" wrote in message
...
Emory,

Have you tried this?

ActiveSheet.Buttons.Select

or

ActiveSheet.Buttons.Delete

This should work on Forms Toolbar Buttons.

Regards,
KL

"Emory Richter" wrote in message
om...
In article ,
says...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory


Chip, Peter, Tom and KL

The buttons are macro buttons from the forms toolbar.
There are a few hundred on the worksheet, I think.

I plugged each suggested code
into the workbook
as a macro
but none seem to do anything on this paticular sheet.
So, for now I will work by hand.

If anyone wants the workbook to play with:

[remove EMORY]

Thank you all for your responses and your efforts.

Emory

"Tom Ogilvy" wrote in message
...
I think you meant
Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
select.Delete

Otherwise, not sure why you introduced oOLEs

I should have clarified they were ad hoc example lines, not intended as a
complete routine. Paraphrasing, the OP mentioned delete all buttons in a
single statement, that would not require setting a reference. But he also
included "change their properties as a group", hence example with a
reference.

Also, in your original response, you said "Very tentitively" as if you
were
contradicting Chip. I just clarified for the OP that Chip was speaking of
commandbuttons and not buttons from the Forms toolbar.

I can't imagine ever "contradicting" Chip, or you for that matter <g. The
reverse - very likely! (and welcomed). But on this rare occasion I didn't
think Chip's answer covered the unknown possibility the OP was talking about
Forms buttons. There must be another word to cover this scenario, but it's
not "contradict". In hindsight, and as the OP has since confirmed he did
indeed mean "Forms", I might have been overly "tentative".

Shame on the OP for not being more specific <g.

(Forms or Controls) I know you meant this light heartedly but I have some
sympathy. Many are understandably confused by the two sets, or unaware of
the distinction. I'm sure if MS were to design a brand new application they
would not be replicated. It's just evolved this way and so I guess they are
there in perpetuity, to confuse generations to come!

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

Yes I realize.

The OP did not specify what sort of buttons, so I thought 60/40 he might
have wanted to reference all Forms' buttons in a statement, as he put
it.

I suppose were his buttons Controls, he could also reference all
OLEObjects
in one go:

Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
ActiveSheet.OLEObjects.Delete

Of course not specific to CommandButtons, limited and could include
something unintended.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

The buttons collection for buttons from the forms toolbar does have a
delete
method

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Chip & Emory,

Very tentitively - following seems to work for buttons from the
Forms
menu:

Sub test()
Dim bts As Buttons

'ActiveSheet.Buttons.Font.ColorIndex = 3

Set bts = ActiveSheet.Buttons
bts.OnAction = "MyMacro"
bts.Copy
Worksheets("Sheet2").Paste
Application.CutCopyMode = False
bts.Delete
End Sub

Sub MyMacro()
MsgBox Application.Caller
End Sub

Regards,
Peter T

"Chip Pearson" wrote in message
...
No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

There must be another word to cover this scenario,

My post in this type of situation might have been something like.

To add to Chips excellent advice regarding commandbuttons from the controls
toolbox toolbar, you may actually be using buttons from the forms toolbar.
If so, delightedly, you can do mass changes using the buttons collection of
the sheet. . . .



--
Regards,
Tom Ogilvy



"Peter T" <peter_t@discussions wrote in message
...

"Tom Ogilvy" wrote in message
...
I think you meant
Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
select.Delete

Otherwise, not sure why you introduced oOLEs

I should have clarified they were ad hoc example lines, not intended as a
complete routine. Paraphrasing, the OP mentioned delete all buttons in a
single statement, that would not require setting a reference. But he also
included "change their properties as a group", hence example with a
reference.

Also, in your original response, you said "Very tentitively" as if you
were
contradicting Chip. I just clarified for the OP that Chip was speaking
of
commandbuttons and not buttons from the Forms toolbar.

I can't imagine ever "contradicting" Chip, or you for that matter <g. The
reverse - very likely! (and welcomed). But on this rare occasion I didn't
think Chip's answer covered the unknown possibility the OP was talking
about
Forms buttons. There must be another word to cover this scenario, but it's
not "contradict". In hindsight, and as the OP has since confirmed he did
indeed mean "Forms", I might have been overly "tentative".

Shame on the OP for not being more specific <g.

(Forms or Controls) I know you meant this light heartedly but I have some
sympathy. Many are understandably confused by the two sets, or unaware of
the distinction. I'm sure if MS were to design a brand new application
they
would not be replicated. It's just evolved this way and so I guess they
are
there in perpetuity, to confuse generations to come!

Regards,
Peter T


"Peter T" <peter_t@discussions wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

Yes I realize.

The OP did not specify what sort of buttons, so I thought 60/40 he
might
have wanted to reference all Forms' buttons in a statement, as he put
it.

I suppose were his buttons Controls, he could also reference all
OLEObjects
in one go:

Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
ActiveSheet.OLEObjects.Delete

Of course not specific to CommandButtons, limited and could include
something unintended.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Chip was speaking of controls from the control toolbox toolbar.

The buttons collection for buttons from the forms toolbar does have
a
delete
method

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Chip & Emory,

Very tentitively - following seems to work for buttons from the
Forms
menu:

Sub test()
Dim bts As Buttons

'ActiveSheet.Buttons.Font.ColorIndex = 3

Set bts = ActiveSheet.Buttons
bts.OnAction = "MyMacro"
bts.Copy
Worksheets("Sheet2").Paste
Application.CutCopyMode = False
bts.Delete
End Sub

Sub MyMacro()
MsgBox Application.Caller
End Sub

Regards,
Peter T

"Chip Pearson" wrote in message
...
No, there is no built in way to select all the controls, or all
the controls of a given type. You have to select them manually
and change their properties as a group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Emory Richter" wrote in message
om...
Is there a statement to select *all* the buttons on a
worksheet
in order to delete them or change their properties as a group?

Thank you,
Emory

I'll try and remember that !

"Tom Ogilvy" wrote in message
...
There must be another word to cover this scenario,

My post in this type of situation might have been something like.

To add to Chips excellent advice regarding commandbuttons from the
controls
toolbox toolbar, you may actually be using buttons from the forms toolbar.
If so, delightedly, you can do mass changes using the buttons collection
of
the sheet. . . .



--
Regards,
Tom Ogilvy



"Peter T" <peter_t@discussions wrote in message
...

"Tom Ogilvy" wrote in message
...
I think you meant
Dim oOLEs As OLEObjects
Set oOLEs = ActiveSheet.OLEObjects
oOLEs.Select
select.Delete

Otherwise, not sure why you introduced oOLEs

I should have clarified they were ad hoc example lines, not intended as
a
complete routine. Paraphrasing, the OP mentioned delete all buttons in a
single statement, that would not require setting a reference. But he
also
included "change their properties as a group", hence example with a
reference.

Also, in your original response, you said "Very tentitively" as if you
were
contradicting Chip. I just clarified for the OP that Chip was
speaking
of
commandbuttons and not buttons from the Forms toolbar.

I can't imagine ever "contradicting" Chip, or you for that matter <g.
The
reverse - very likely! (and welcomed). But on this rare occasion I
didn't
think Chip's answer covered the unknown possibility the OP was talking
about
Forms buttons. There must be another word to cover this scenario, but
it's
not "contradict". In hindsight, and as the OP has since confirmed he did
indeed mean "Forms", I might have been overly "tentative".

Shame on the OP for not being more specific <g.

(Forms or Controls) I know you meant this light heartedly but I have
some
sympathy. Many are understandably confused by the two sets, or unaware
of
the distinction. I'm sure if MS were to design a brand new application
they
would not be replicated. It's just evolved this way and so I guess they
are
there in perpetuity, to confuse generations to come!

Regards,
Peter T

snip <

In article ,
says...
In article ,
says...
Is there a statement to select *all* the buttons on a worksheet
in order to delete them or change their properties as a group?

The buttons are macro buttons from the forms toolbar.
There are a few hundred on the worksheet, I think.

Thank you all for your responses and your efforts.

Emory

Yes!
ActiveSheet.Buttons.Delete
or
Worksheets("Sheet1").Buttons.Select
Selection.Delete
Both work fine.

KL and Tom
Thanks for your help.

Emory