Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) -- 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code cleanup help
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@excelforu m-nospam.com wrote in message news:peter.thompson.220n6n_1137899405.3314@excelfo rum-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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code cleanup help
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code cleanup help
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@excelforu m-nospam.com wrote in message news:peter.thompson.220q6y_1137903301.6866@excelfo rum-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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code cleanup help
Got it, thanks for the help and education! 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data cleanup | Excel Worksheet Functions | |||
Code cleanup | Excel Programming | |||
Code cleanup help please | Excel Programming | |||
Leo Heuser format cleanup code, and a question | Excel Programming | |||
Source Data Code Cleanup Help | Excel Programming |