Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data cleanup sjs Excel Worksheet Functions 1 September 16th 09 03:37 PM
Code cleanup peter.thompson[_53_] Excel Programming 2 January 18th 06 07:16 AM
Code cleanup help please peter.thompson[_8_] Excel Programming 5 December 22nd 05 08:08 AM
Leo Heuser format cleanup code, and a question GatesAntichrist Excel Programming 3 July 2nd 04 04:18 AM
Source Data Code Cleanup Help John Michl[_2_] Excel Programming 2 August 18th 03 09:01 PM


All times are GMT +1. The time now is 04:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"