Command buttons in ExcelVBA

[PeteG]

Hi,
I have several command buttons on a worksheet. I want to be able to go through and change the 'enabled' property for all of them and was expecting to be able to do something like this:
dim cmdBtn as commandbutton
for each cmdbtn in sheet1.controls
cmdbtn.enabled = not cmdbtn.enabled
next
BUT, I can't find a controls collection or anything like it to use.
Any help would be appreciated
Cheers

 

[LoNeRaVeR]

PeteG:

As far as I know there isn't an Enabled property for Command Buttons inside a worksheet since they are treated as DrawingObjects. You may protect just them though so they cannot be clicked by protecting the sheet's DrawingObjects. Try the following code.

Sub Protect_Objects()
Dim iCount as Integer
With ActiveSheet
.Protect DrawingObjects:=False
For iCount = 1 To .Shapes.Count
.Shapes(iCount).Locked = True
Next iCount
.Protect DrawingObjects:=True
End With
End Sub

If you do discover another way, please let me know.

Regards, LoNeRaVeR

 

[DarkSun]

LoNeRaVeR:
There is enabled on a VB Command Button, but not on a Forms Command Button.

PeteG:
The collection you are looking for is OLEObjects.

Example:
Dim cmdbut As Object

For Each cmdbut In Sheet1.OLEObjects
cmdbut.Enabled = False
Next


This code will disable all objects. You need to change the code to only disable command button. Have a little play and you should be able to do it.

 

[PeteG]

Thanks for the responses. I had already tried the OLEObjects collection but I had some unusual results and I've just worked out why. I initially had two buttons on the sheet that I copied to make five. I gave them all individual names but when I looped through the OLEObjects collection, it correctly found five objects but they were all named either commandbutton1 or commandbutton2 and so only the first two buttons were referenced (sounds weird but there you go).
I've now recreated the five buttons manually. Though I have changed the name properties to the names that I want, looping through OLEObjects still returns the original button names commandbutton1, 2, 3 and so on but it does work so I'm happy!
Still not sure why it doesn't pick up the actual 'name' property of my command buttons but I suppose it's because OLEObjects is a generic collection and doesn't contain/expose all the properties of command buttons.....?

Thanks again

Pete