Excel Userform: How to loop and Tab order

E

egrospe

Board Regular
Joined
Feb 10, 2016
Messages
54
Hello everyone! I'm currently creating an excel userform. My form will contain different questions that are answered with YES or NO using option buttons. If clicked YES, one text box with a follow up question will enable and the other will disable. Same when clicked NO, the other text box will enable and the other will disable.

Now, that works well however, if I click yes, one box will disable and then when I click no, the other box will disable as well. Is there a way to have the command loop so that when the user clicks yes one text box will disable then when clicked no the other text box will disable but enable to other one?

Here's my code for it:

Private Sub rdoQ1NO_Click()

'This is the code to disable one text box when NO is clicked
txtComment.Enabled = True
txtExecutionDate.Enabled = False
txtExecutionDate.BackColor = &H8000000F

End Sub


Private Sub rdoQ1YES_Click()

'This is the code to disable one text box when NO is clicked
txtExecutionDate.Enabled = True
txtComment.Enabled = False
txtComment.BackColor = &H8000000F


End Sub


My other question is when I'm running the form, pressing the tab keys makes the text indicator move from one box to another, however I didn't create my text boxes in order so it jumps around in different order. Is there a way to fix that order or do I just have to create my text boxes in order?

Thank you!!!


2r6ekj5.jpg
 
Sort by date Sort by votes

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Domenic said:
That's great, thanks for the feedback!

Cheers!
Click to expand...

Hi Domenic,

Sorry to bother again but I just ran into a new problem. I have a "Get Data" command button that gets all the data from the excel sheet back to the userform, it works will with rdoQ1YES and rdoQ1NO (meaning it transfer it back to the userform) However, when there are values in the cells that corresponds to the other radio buttons, it gives me this error.

"Run-time error ‘2110’: Can’t move focus to the control because it is invisible, not enabled, or of a type that does not accept focus"

What am I missing?

Here's my code for the radio buttons as you suggested:

Code: 
Private Sub rdoQ1NO_Click()

SetEnable Me.txtExecutionDate, False
SetEnable Me.txtExplanation1, True

End Sub

Private Sub rdoQ1YES_Click()

SetEnable Me.txtExecutionDate, True
SetEnable Me.txtExplanation1, False

End Sub

Private Sub rdoQ2YES_Click()

SetEnable Me.txtACQ, True
SetEnable Me.txtRELO, True
SetEnable Me.txtAPP, True
SetEnable Me.txtPJM, True
SetEnable Me.txtSPS, True
SetEnable Me.txtPPM, True
SetEnable Me.txtDEMO, True
SetEnable Me.txtSUB, True
SetEnable Me.txtExplanation2, False

End Sub

Private Sub rdoQ2NO_Click()

SetEnable Me.txtACQ, False
SetEnable Me.txtRELO, False
SetEnable Me.txtAPP, False
SetEnable Me.txtPJM, False
SetEnable Me.txtSPS, False
SetEnable Me.txtPPM, False
SetEnable Me.txtDEMO, False
SetEnable Me.txtSUB, False
SetEnable Me.txtExplanation2, True

End Sub

Private Sub rdoQ4YES_Click()

SetEnable Me.txtClientCode, True
SetEnable Me.txtContactName, False

End Sub


Private Sub rdoQ4NO_Click()

SetEnable Me.txtClientCode, False
SetEnable Me.txtContactName, True

End Sub




Private Sub UserForm_Initialize()

'Code to load combo box containing emails
For Each blah In [Route_List]
    Me.cmbRouteList.AddItem blah
Next blah

For Each blah In [Route_List]
    Me.cmbRouteList2.AddItem blah
Next blah

End Sub



Sub SetEnable(ByVal tb As msforms.TextBox, ByVal bEnabled As Boolean)

    With tb
        .Enabled = bEnabled
        .BackColor = IIf(bEnabled, &H80000005, &H8000000F)
        If .Enabled Then .SetFocus
    End With
    
End Sub

And here's the code for my 'Get Data' command button:

Code: 
Private Sub cmdGetData_Click()

txtContractID.Text = Range("E4").Value
txtContractName.Text = Range("E5").Value
txtSubmitter.Text = Range("E6").Value
txtExecutionDate.Text = Range("G13").Value
txtExplanation1.Text = Range("G15").Value
txtACQ.Text = Range("H28").Value
txtRELO.Text = Range("H29").Value
txtAPP.Text = Range("H30").Value
txtPJM.Text = Range("H31").Value
txtSPS.Text = Range("J28").Value
txtPPM.Text = Range("J29").Value
txtDEMO.Text = Range("J30").Value
txtSUB.Text = Range("J31").Value
txtExplanation2.Text = Range("G33").Value
txtQ3.Text = Range("G43").Value
txtClientCode.Text = Range("G46").Value
txtContactName.Text = Range("G48").Value
txtComments1.Text = Range("G51").Value

cmbRouteList.Text = Range("G20").Value 

'code to get back the value of radio buttons
rdoQ1YES.Value = CBool(Sheets("Form").Range("G11").Value = rdoQ1YES.Caption)
rdoQ1NO.Value = CBool(Sheets("Form").Range("G11").Value = rdoQ1NO.Caption)
rdoQ2YES.Value = CBool(Sheets("Form").Range("G25").Value = rdoQ2YES.Caption)
rdoQ2NO.Value = CBool(Sheets("Form").Range("G25").Value = rdoQ2NO.Caption)
rdoQ4YES.Value = CBool(Sheets("Form").Range("G44").Value = rdoQ4YES.Caption)
rdoQ4NO.Value = CBool(Sheets("Form").Range("G44").Value = rdoQ4NO.Caption)


Thank you :)
 
Last edited:
Upvote 0
Hi Domenic,

As I was testing the userform, it seems like when the tab (within a multi page) containing the radio buttons is not the active one, when I press the 'Get Data' Command button, it works just fine.

So it seems like the .SetFocus isn't working when the tab isn't 'active' per se.
 
Upvote 0
Ah yes, I see what you mean. I forgot that your controls are within a multipage control. So let's see. When you click on the "Get Data" commandbutton, the click event for your option buttons may get triggered and the SetEnable procedure called. I'm assuming that this is what you want, except that in this case you don't want focus to be set for the control, right? If so, we can modify the code as follows.

First, declare the following variable at the module level (at the top of the module before all procedures)...

Code: 
Dim bDisableSetFocus As Boolean

Then, change the SetEnable procedure as follows...

Code: 
Sub SetEnable(ByVal tb As msforms.TextBox, ByVal bEnabled As Boolean)

    With tb
        .Enabled = bEnabled
        .BackColor = IIf(bEnabled, &H80000005, &H8000000F)
[COLOR=#ff0000]        If Not bDisableSetFocus Then
            If .Enabled Then .SetFocus
        End If[/COLOR]
    End With
    
End Sub

And lastly, change the code for your commandbutton as follows...

Code: 
Private Sub cmdGetData_Click()

    txtContractID.Text = Range("E4").Value
    txtContractName.Text = Range("E5").Value
    txtSubmitter.Text = Range("E6").Value
    'etc
    '
    '
    '

    [COLOR=#ff0000]bDisableSetFocus = True[/COLOR]
    rdoQ1YES.Value = CBool(Sheets("Form").Range("G11").Value = rdoQ1YES.Caption)
    rdoQ1NO.Value = CBool(Sheets("Form").Range("G11").Value = rdoQ1NO.Caption)
    rdoQ2YES.Value = CBool(Sheets("Form").Range("G25").Value = rdoQ2YES.Caption)
    '
    '
    '
   [COLOR=#ff0000] bDisableSetFocus = False[/COLOR]

End Sub

Does this help?
 
Upvote 0
Domenic said:
Ah yes, I see what you mean. I forgot that your controls are within a multipage control. So let's see. When you click on the "Get Data" commandbutton, the click event for your option buttons may get triggered and the SetEnable procedure called. I'm assuming that this is what you want, except that in this case you don't want focus to be set for the control, right? If so, we can modify the code as follows.

First, declare the following variable at the module level (at the top of the module before all procedures)...

Code: 
Dim bDisableSetFocus As Boolean

Then, change the SetEnable procedure as follows...

Code: 
Sub SetEnable(ByVal tb As msforms.TextBox, ByVal bEnabled As Boolean)

    With tb
        .Enabled = bEnabled
        .BackColor = IIf(bEnabled, &H80000005, &H8000000F)
[COLOR=#ff0000]        If Not bDisableSetFocus Then
            If .Enabled Then .SetFocus
        End If[/COLOR]
    End With
    
End Sub

And lastly, change the code for your commandbutton as follows...

Code: 
Private Sub cmdGetData_Click()

    txtContractID.Text = Range("E4").Value
    txtContractName.Text = Range("E5").Value
    txtSubmitter.Text = Range("E6").Value
    'etc
    '
    '
    '

    [COLOR=#ff0000]bDisableSetFocus = True[/COLOR]
    rdoQ1YES.Value = CBool(Sheets("Form").Range("G11").Value = rdoQ1YES.Caption)
    rdoQ1NO.Value = CBool(Sheets("Form").Range("G11").Value = rdoQ1NO.Caption)
    rdoQ2YES.Value = CBool(Sheets("Form").Range("G25").Value = rdoQ2YES.Caption)
    '
    '
    '
   [COLOR=#ff0000] bDisableSetFocus = False[/COLOR]

End Sub

Does this help?
Click to expand...

That did the trick!! You sir are a genius! Thank you very much for all your help. I appreciate it.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

hicksi
  • Solved
how to identify control in focus
Replies
7
Views
128
hicksi
hicksi
A
  • Question Question
How to Ungroup option buttons
Replies
3
Views
222
RoryA
RoryA
M
  • Solved
VBA Userform: Checkbox & IF statement
Replies
7
Views
828
Akuini
Akuini
P
  • Question Question
If and ElseIf confusion !
Replies
5
Views
325
Micron
M
L
  • Solved
Getting Userform Command Buttons to Work with a BeforeSave VBA Macro
2
Replies
19
Views
261
Logit
Logit

Forum statistics

Threads
1,226,112
Messages
6,189,029
Members
453,520
Latest member
packrat68

Share this page

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