Code cleanup help

I want to disable 30 commandbuttons on a sheet. At the moment I'm using the following code which works, but I'm sure there must be a better way.

Sheet7.CommandButton1.Enabled = False
Sheet7.CommandButton2.Enabled = False

(repeated to comandbutton30)

Any help appreciated (If you could also walk me through the code as to what its doing that would be great for my learning!)

Cheers

Peter (slogging through my first VBA project)

Hi Peter,

Try:

'=============>>
Public Sub Tester002()
Dim obj As OLEObject

For Each obj In Sheets("Sheet7").OLEObjects
If TypeOf obj.Object Is MSForms.CommandButton Then
obj.Object.Enabled = False
End If
Next obj

End Sub
'<<=============


---
Regards,
Norman



"peter.thompson"
<peter.thompson.220n6n_1137899405.3314@excelforum-nospam.com> wrote in
message news:peter.thompson.220n6n_1137899405.3314@excelforum-nospam.com...
>
> I want to disable 30 commandbuttons on a sheet. At the moment I'm using
> the following code which works, but I'm sure there must be a better
> way.
>
> Sheet7.CommandButton1.Enabled = False
> Sheet7.CommandButton2.Enabled = False
>
> (repeated to comandbutton30)
>
> Any help appreciated (If you could also walk me through the code as to
> what its doing that would be great for my learning!)
>
> Cheers
>
> Peter (slogging through my first VBA project)
>
>
> --
> peter.thompson
> ------------------------------------------------------------------------
> peter.thompson's Profile:
> http://www.excelforum.com/member.php...o&userid=29686
> View this thread: http://www.excelforum.com/showthread...hreadid=503729
>

Thanks Norman,

Wouldn't work with Sheets("Sheet7").OLEobjects syntax

but does work with plain Sheet7.OLEObjects

Why is this the case?

Again, thanks for all the help on my first VBA project!

Cheers

Peter

Hi Peter,

> Wouldn't work with Sheets("Sheet7").OLEobjects syntax
Here "Sheet7" refers to the sheet named "Sheet7".

> but does work with plain Sheet7.OLEObjects
Here Sheet7 refers to the sheet whose codename is Sheet7

If a sheet's name is changed, then the sheet name and code name will
differ.You are using the sheet's code name and, consquently, your code will
work irrespective of any change to the sheets name.


---
Regards,
Norman



"peter.thompson"
<peter.thompson.220q6y_1137903301.6866@excelforum-nospam.com> wrote in
message news:peter.thompson.220q6y_1137903301.6866@excelforum-nospam.com...
>
> Thanks Norman,
>
> Wouldn't work with Sheets("Sheet7").OLEobjects syntax
>
> but does work with plain Sheet7.OLEObjects
>
> Why is this the case?
>
> Again, thanks for all the help on my first VBA project!
>
> Cheers
>
> Peter
>
>
> --
> peter.thompson
> ------------------------------------------------------------------------
> peter.thompson's Profile:
> http://www.excelforum.com/member.php...o&userid=29686
> View this thread: http://www.excelforum.com/showthread...hreadid=503729
>

Got it, thanks for the help and education!

Cheers

Peter