Buttons - Select All ?

[Emory Richter]

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

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

 

[Peter T]

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

 

[Tom Ogilvy]

Chip was speaking of controls from the control toolbox toolbar.

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

 

[KL]

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

 

[KL]

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

 

[Peter T]

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

 

[Emory Richter]

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:
(e-mail address removed)
[remove EMORY]

Thank you all for your responses and your efforts.

Emory

 

[KL]

Emory,

Have you tried this?

ActiveSheet.Buttons.Select

or

ActiveSheet.Buttons.Delete

This should work on Forms Toolbar Buttons.

Regards,
KL

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:
(e-mail address removed)
[remove EMORY]

Thank you all for your responses and your efforts.

Emory

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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

Emory,

Have you tried this?

ActiveSheet.Buttons.Select

or

ActiveSheet.Buttons.Delete

This should work on Forms Toolbar Buttons.

Regards,
KL

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:
(e-mail address removed)
[remove EMORY]

Thank you all for your responses and your efforts.

Emory

 

[Peter T]

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

 

[Tom Ogilvy]

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

 

[Peter T]

I'll try and remember that !

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



speaking
snip <

 

[Emory Richter]

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