Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 129
Default Enabling/Disabling multiple option buttons

I have an excel worksheet ("Sheet 1") with a number of Optionbuttons
all of which I want to disable
via macro which runs from another worksheet ("Sheet 2").

So I want to do something like this:

For i = 1 To 7
Sheets("Sheet 1").Controls("OptionButton" & i).Enable = False
Next i

Will this work?
Or am I missing something?






  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default Enabling/Disabling multiple option buttons

Maybe

Sub Switch_Em()
Dim ws As Worksheet
Dim obj As OLEObject
Set ws = Sheets("Sheet1")
For Each obj In ws.OLEObjects
If TypeName(obj.Object) = "OptionButton" Then
obj.Object.Value = False
End If
Next obj
End Sub

Mike

"Will" wrote:

I have an excel worksheet ("Sheet 1") with a number of Optionbuttons
all of which I want to disable
via macro which runs from another worksheet ("Sheet 2").

So I want to do something like this:

For i = 1 To 7
Sheets("Sheet 1").Controls("OptionButton" & i).Enable = False
Next i

Will this work?
Or am I missing something?






  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Enabling/Disabling multiple option buttons

You didn't say where your OptionButtons came from (the Forms or Controls
Toolbox toolbars). If from the Forms toolbar, you can disable them all with
a macro containing just a single line of code...

Sub DisableFormsOptionButtons()
Worksheets("Sheet 1").OptionButtons.Enabled = False
End Sub

If they are from the Controls Toolbox toolbar, then this macro will do what
you want...

Sub DisableActiveXOptionButtons()
Dim OptBtn As OLEObject
For Each OptBtn In Worksheets("Sheet 1").OLEObjects
OptBtn.Object.Enabled = False
Next
End Sub

And if you both and want to disable them all, then use this macro...

Sub DisableAllOptionButtons()
Dim OptBtn As OLEObject
With Worksheets("Sheet 1")
.OptionButtons.Enabled = False
For Each OptBtn In .OLEObjects
OptBtn.Object.Enabled = False
Next
End With
End Sub

--
Rick (MVP - Excel)


"Will" wrote in message
...
I have an excel worksheet ("Sheet 1") with a number of Optionbuttons
all of which I want to disable
via macro which runs from another worksheet ("Sheet 2").

So I want to do something like this:

For i = 1 To 7
Sheets("Sheet 1").Controls("OptionButton" & i).Enable = False
Next i

Will this work?
Or am I missing something?







  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 129
Default Enabling/Disabling multiple option buttons

Thanks for your help Rick.
All my OptionButtons come from the ActiveX Controls toolbox.

Another question:
If I don't want to disable ALL Optionbuttons, but only a subset.
-- for example OptionButton10 to OptionButton20.

Is this possible?
Is there a For ... Next routine I can use?

Thanks,
Will


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Enabling/Disabling multiple option buttons



Dim OLEObj As OLEObject
Dim iCtr As Long
For iCtr = 10 To 20
Worksheets("Sheet1").OLEObjects("OptionButton" & iCtr).Enabled = False
Next iCtr

Will wrote:

Thanks for your help Rick.
All my OptionButtons come from the ActiveX Controls toolbox.

Another question:
If I don't want to disable ALL Optionbuttons, but only a subset.
-- for example OptionButton10 to OptionButton20.

Is this possible?
Is there a For ... Next routine I can use?

Thanks,
Will


--

Dave Peterson
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
Enabling Option Buttons - Control Andreww Excel Programming 2 May 23rd 07 10:44 AM
Userforms: Disabling - Enabling the X Lauri Excel Programming 4 May 12th 06 07:02 PM
Disabling/Enabling Macros HRobertson Excel Programming 2 October 24th 03 04:07 PM
Enabling/Disabling Macros David Excel Programming 6 August 12th 03 06:21 PM
Option buttons - disabling Ann Excel Programming 1 July 23rd 03 01:04 AM


All times are GMT +1. The time now is 06:04 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"