 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006Code 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)  Signaturepeter.thompson 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 > 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 [quoted text clipped - 11 lines] > > Peter (slogging through my first VBA project) 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 Signaturepeter.thompson 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 > Thanks Norman, > [quoted text clipped - 9 lines] > > Peter |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.