Graying out a large number of textboxes at once.

mthornburg

New Member
Joined
Jun 11, 2008
Messages
8
Hi.
I'm currently building a form in Excel that has some questions only necessary if the user answers a certain way to previous ones (i.e. "If you said yes, then..."). I created a macro to disable and gray out the questions that the user does not need to fill out.
Code:
Private Sub OptionButton33_Change()
If OptionButton33.Value = True Then
OptionButton34.Enabled = False
OptionButton34.ForeColor = SystemColorConstants.vbGrayText
OptionButton35.Enabled = False
OptionButton35.ForeColor = SystemColorConstants.vbGrayText

...

OptionButton44.Enabled = False
OptionButton44.ForeColor = SystemColorConstants.vbGrayText
OptionButton45.Enabled = False
OptionButton45.ForeColor = SystemColorConstants.vbGrayText
Else
OptionButton34.Enabled = True
OptionButton34.ForeColor = SystemColorConstants.vbWindowText
OptionButton35.Enabled = True
OptionButton35.ForeColor = SystemColorConstants.vbWindowText

...

OptionButton44.Enabled = True
OptionButton44.ForeColor = SystemColorConstants.vbWindowText
OptionButton45.Enabled = True
OptionButton45.ForeColor = SystemColorConstants.vbWindowText
End If
End Sub
However, I was wondering if there was a more efficient way to do this than writing every single control's name out. All of the buttons above are grouped together with the Groupname being "Question9" . Is there a way to tell all of the buttons with a certain Groupname to do something instead of instructing each individually?

I looked all over for an answer to this. Thanks in advance.
Matt
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
You could place each question group within a frame and then grey out all question groups by frame. This would reduce the complexity somewhat. Or you could create a function such as...

Download example to see the function work...
324370.0612080023.zip

Code:
Private Sub DisableGroup(GroupName As String)
    Dim c As Control
 
    For Each c In Me.Controls
        If TypeOf c Is MSForms.OptionButton Then
            If c.GroupName = GroupName Then
                c.Enabled = False
            End If
        End If
    Next
End Sub
 
Upvote 0
I think I figured out how to work this out using loops. I'm not quite sure why the code below does not work, however. What's supposed to happen is that when the value of OptionButton33 changes, the attributes of OptionButton34 through OptionButton39 are supposed to change as indicated.

Am I defining the Shapes incorrectly? I tried Me.Controls("OptionButton" & i) with no success either. All of the controls are located on Sheet 1 of an Excel workbook, if that is of any help.

Thank you.

Code:
Private Sub OptionButton33_Change()
Dim i As Integer
If OptionButton33.Value = True Then
For i = 34 To 39
ActiveSheet.Shapes("OptionButton" & i).ForeColor = SystemColorConstants.vbGrayText
ActiveSheet.Shapes("OptionButton" & i).Enabled = False
Next i
Else
For i = 34 To 39
ActiveSheet.Shapes("OptionButton" & i).ForeColor = SystemColorConstants.vbWindowText
ActiveSheet.Shapes("OptionButton" & i).Enabled = True
Next i
End If
End Sub
 
Upvote 0
I misunderstood your initial post and thought that you were using controls on a userform.
 
Upvote 0
Sorry. I should have been clearer.

For the life of me, I still can't figure out what's wrong with this code.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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