Graying out a large number of textboxes at once.

[mthornburg]

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.

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

 

[dafan]

Can't you just use Question9.Enabled = False ?

 

[Legacy 98055]

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

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

 

[mthornburg]

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.

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

 

[mthornburg]

Should I use controls instead of shapes? Me.Controls does not seem to work.

 

[Legacy 98055]

I misunderstood your initial post and thought that you were using controls on a userform.

 

[mthornburg]

Sorry. I should have been clearer.

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

 

[Legacy 98055]

Please send me you workbook if possible...

tstom@fuse.net