Can you change a button group lock options to True/False with 1 command?

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
619
Office Version
  1. 2011
Platform
  1. MacOS
Just wondering if I can shorten my code a tad by changing grouped buttons with 1 command instead of two.

For example: If you have a group of two radio buttons (for this example I'll call the group "NewEmp") and you want to lock or unlock them, do you have to use two commands (i.e. Me.NewEmpYes.Locked = False, Me.NewEmpNo.Locked = False) or is there a way to lock/unlock them both with one command since they're grouped together?

Thanks.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
There is not a single command for that.
You have to go through the controls, ask if it is an option button and if it is within the group, then change the status, for example:

Code:
  Dim Cntrl As Control
  For Each Cntrl In Controls
    If TypeOf Cntrl Is MSForms.OptionButton Then
      If Cntrl.GroupName = "NewEmp" Then
        Cntrl.Locked = False
      End If
    End If
  Next
 
Last edited:
Upvote 0
Thanks Dante. It worked for one set of buttons.

What in your opinion would be the best way to approach it so it works with multiple groups?
By that, I mean there are several groups of buttons that need to be either False or True at the moment that code is called upon. Right now, your code works with one set of buttons. If I were to add ElseIf, then it will bypass the other button groups.

Thanks again!
 
Upvote 0
It may be so:

Code:
 Dim Cntrl As Control
  For Each Cntrl In Controls
    If TypeOf Cntrl Is MSForms.OptionButton Then
      Select Case Cntrl.GroupName
        Case "NewEmp": Cntrl.Locked = [COLOR=#ff0000]False[/COLOR]
        Case "[COLOR=#0000ff]Group2[/COLOR]": Cntrl.Locked = [COLOR=#008000]True[/COLOR]
        Case "[COLOR=#0000ff]Group3[/COLOR]": Cntrl.Locked = [COLOR=#ff0000]False[/COLOR]
      End If
    End If
  Next
 
Upvote 0
Awesome! This worked. Thanks Dante. Funny, I was thinking about using Select Case, but didn't know if that would work for this.

Had to edit the code a little. The first "End If" should be "End Select" for those who may come across this thread down the road.
 
Upvote 0
Awesome! This worked. Thanks Dante. Funny, I was thinking about using Select Case, but didn't know if that would work for this.

Had to edit the code a little. The first "End If" should be "End Select" This is correct :oops:


for those who may come across this thread down the road.


Must be:
Code:
 Dim Cntrl As Control
  For Each Cntrl In Controls
    If TypeOf Cntrl Is MSForms.OptionButton Then
      Select Case Cntrl.GroupName
        Case "NewEmp": Cntrl.Locked = False
        Case "Group2": Cntrl.Locked = True
        Case "Group3": Cntrl.Locked = False
[COLOR=#ff0000]      End Select[/COLOR]
    End If
  Next
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top