Loop thru user form controls

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,362
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have four option buttons on my user form and need to enable/disable some text boxes based on which option is selected. The code below works fine, but before I code the same for the other there option buttons, can this be reconstructed into the loop thru?

Code:
Private Sub Opt1_Click()
    If Me.Opt1 Then
        Me.txtQtr1.Enabled = True
        Me.txtQtr1e.Enabled = True
        Me.txtQtr1n.Enabled = True
        
        Me.txtQtr2.Enabled = False
        Me.txtQtr2e.Enabled = False
        Me.txtQtr2n.Enabled = False
        
        Me.txtQtr3.Enabled = False
        Me.txtQtr3e.Enabled = False
        Me.txtQtr3n.Enabled = False
        
        Me.txtQtr4.Enabled = False
        Me.txtQtr4e.Enabled = False
        Me.txtQtr4n.Enabled = False
    End If
End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
One option might be to make all the relevant textboxes disabled on Initialise & then use something like this
Code:
Private Sub Opt1_Click()
   Me.txtQtr1.Enabled = Me.Opt1
   Me.txtQtr1e.Enabled = Me.Opt1
   Me.txtQtr1n.Enabled = Me.Opt1
End Sub
for each option button
 
Last edited:
Upvote 0
Thanks Fluff. I applied the code to each Opt_Click procedure with changing the numbers, but unless I'm missing something, how does that apply to disabling txtQtr1 when I select option 2?
 
Last edited:
Upvote 0
Do you want to set which group of textboxes (e.g. txtQtr1, txtQtr1e, txtQtr1n etc) is enabled based on which option button (e.g. Opt1) is set to true?


Perhaps something like this,
Code:
Sub EnableQuarter(lngQtr As Long)
Dim I As Long

    For I = 1 To 4

        Me.Controls("txtQtr" & I).Enabled = lngQtr = I
        Me.Controls("txtQtr" & I & "e").Enabled = lngQtr = I
        Me.Controls("txtQtr" & I & "n").Enabled = lngQtr = I
   Next I

End Sub
which could be called from the option buttons like this.
Code:
Private Sub Opt1_Click()
    If Me.Opt1 Then
        EnableQuarter 1
    End If
End Sub

Private Sub Opt2_Click()
    If Me.Opt2 Then
        EnableQuarter 2
    End If
End Sub

Private Sub Opt3_Click()
    If Me.Opt3 Then
        EnableQuarter 3
    End If
End Sub

Private Sub Opt4_Click()
    If Me.Opt4 Then
        EnableQuarter 4
    End If
End Sub
 
Upvote 0
Good point.
How about something like
Code:
Private Sub Opt1_Click()
        Me.txtQtr1.Enabled = Me.Opt1
        Me.txtQtr1e.Enabled = Me.Opt1
        Me.txtQtr1n.Enabled = Me.Opt1
        
        Me.txtQtr2.Enabled = Me.Opt2
        Me.txtQtr2e.Enabled = Me.Opt2
        Me.txtQtr2n.Enabled = Me.Opt2
        
        Me.txtQtr3.Enabled = False
        Me.txtQtr3e.Enabled = False
        Me.txtQtr3n.Enabled = False
        
        Me.txtQtr4.Enabled = False
        Me.txtQtr4e.Enabled = False
        Me.txtQtr4n.Enabled = False
    End If
End Sub
Adding in the other option buttons & put this code in each option button click
 
Upvote 0
Thanks Fluff. Norie, thanks, that does the trick.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
If I could, another request please? With the enabling/disabling, how can you turn on the textbox backcolor as the different option buttons are selected?
 
Upvote 0
Which colour for disabled, and which for enabled?
 
Upvote 0
A light blue for enabled and vbwhite for disabled.
 
Upvote 0

Forum statistics

Threads
1,214,121
Messages
6,117,846
Members
448,782
Latest member
lepaulek

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