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

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 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

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

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

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

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