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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
You should be able to set the controls' properties:

TabStop <true false="">

and

TabIndex ordinal number<ordinal number="" of="" the="" tab="" stop="">

To specify the tab stops and the order</ordinal></true>
 
Last edited:
Upvote 0
Hi,
give this a try & see if does what you want.

Place code in your Forms code page:

Code: 
Private Sub rdoQ1NO_Click()
    SetEnable Me
End Sub




Private Sub rdoQ1YES_Click()
    SetEnable Me
End Sub


Sub SetEnable(ByVal Form As Object)


    With Form.txtComment
        .Enabled = Not Form.rdoQ1YES.Value
        .BackColor = IIf(Not Form.rdoQ1YES.Value, &H80000005, &H8000000F)
        If .Enabled Then .SetFocus
    End With
    
    With Form.txtExecutionDate
        .Enabled = Form.rdoQ1YES.Value
        .BackColor = IIf(Form.rdoQ1YES.Value, &H80000005, &H8000000F)
        If .Enabled Then .SetFocus
    End With


End Sub

You can resolve your Tab Order problem from VBA Editor - select the Form & then from menu View > Tab Order. then adjust as required.

Dave
 
Last edited:
Upvote 0
dmt32 said:
Hi,
give this a try & see if does what you want.

Place code in your Forms code page:

Code: 
Private Sub rdoQ1NO_Click()
    SetEnable Me
End Sub




Private Sub rdoQ1YES_Click()
    SetEnable Me
End Sub


Sub SetEnable(ByVal Form As Object)


    With Form.txtComment
        .Enabled = Not Form.rdoQ1YES.Value
        .BackColor = IIf(Not Form.rdoQ1YES.Value, &H80000005, &H8000000F)
        If .Enabled Then .SetFocus
    End With
    
    With Form.txtExecutionDate
        .Enabled = Form.rdoQ1YES.Value
        .BackColor = IIf(Form.rdoQ1YES.Value, &H80000005, &H8000000F)
        If .Enabled Then .SetFocus
    End With


End Sub

You can resolve your Tab Order problem from VBA Editor - select the Form & then from menu View > Tab Order. then adjust as required.

Dave
Click to expand...

Hi there,

I'm sort of confused where to place these codes. Do I replace my existing codes for Private Sub rdoQ1NO_Click() with just "SetEnable Me"?

Also, I don't know if this matters but my form changed quite a bit. The radio buttons are now located inside a multipage object... Does that make a difference?

Thanks again!
 
Upvote 0
You'll need to replace both your subs with all three of those subs provided to you by Dave, and they'll need to be placed in the same place as your original subs, that is, in the code module for the userform. And placing your option buttons within a multipage control shouldn't make a difference in this case.
 
Last edited:
Upvote 0
Domenic said:
You'll need to replace both your subs with all three of those subs provided to you by Dave, and they'll need to be placed in the same place as your original subs, that is, in the code module for the userform. And placing your option buttons within a multipage control shouldn't make a difference in this case.
Click to expand...

It works now! Thank you Domenic and Dave!
 
Upvote 0
Domenic said:
That's great to hear.

Cheers!
Click to expand...

Quick follow up question, if I have other radio buttons with the same function in a different multipage within the same userform, is it the same concept? Do I just have to repeat the codes Dave provided?
 
Last edited:
Upvote 0
Yes, same sort of concept. Although, depending on what you have, it can be approached differently. Maybe you can adopt the following example...

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ1YES_Click()
    SetEnable Me.txtExecutionDate, [COLOR=darkblue]True[/COLOR]
    SetEnable Me.txtComment, [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ1NO_Click()
    SetEnable Me.txtExecutionDate, [COLOR=darkblue]False[/COLOR]
    SetEnable Me.txtComment, [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ2YES_Click()
    SetEnable Me.txtExecutionDate2, [COLOR=darkblue]True[/COLOR]
    SetEnable Me.txtComment2, [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ2NO_Click()
    SetEnable Me.txtExecutionDate2, [COLOR=darkblue]False[/COLOR]
    SetEnable Me.txtComment2, [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=green]'Other click event handlers for other option buttons[/COLOR]
[COLOR=green]'[/COLOR]
'

[COLOR=darkblue]Sub[/COLOR] SetEnable([COLOR=darkblue]ByVal[/COLOR] tb [COLOR=darkblue]As[/COLOR] msforms.TextBox, [COLOR=darkblue]ByVal[/COLOR] bEnabled [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])

    [COLOR=darkblue]With[/COLOR] tb
        .Enabled = bEnabled
        .BackColor = IIf(bEnabled, &H80000005, &H8000000F)
        [COLOR=darkblue]If[/COLOR] .Enabled [COLOR=darkblue]Then[/COLOR] .SetFocus
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Domenic said:
Yes, same sort of concept. Although, depending on what you have, it can be approached differently. Maybe you can adopt the following example...

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ1YES_Click()
    SetEnable Me.txtExecutionDate, [COLOR=darkblue]True[/COLOR]
    SetEnable Me.txtComment, [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ1NO_Click()
    SetEnable Me.txtExecutionDate, [COLOR=darkblue]False[/COLOR]
    SetEnable Me.txtComment, [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ2YES_Click()
    SetEnable Me.txtExecutionDate2, [COLOR=darkblue]True[/COLOR]
    SetEnable Me.txtComment2, [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] rdoQ2NO_Click()
    SetEnable Me.txtExecutionDate2, [COLOR=darkblue]False[/COLOR]
    SetEnable Me.txtComment2, [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=green]'Other click event handlers for other option buttons[/COLOR]
[COLOR=green]'[/COLOR]
'

[COLOR=darkblue]Sub[/COLOR] SetEnable([COLOR=darkblue]ByVal[/COLOR] tb [COLOR=darkblue]As[/COLOR] msforms.TextBox, [COLOR=darkblue]ByVal[/COLOR] bEnabled [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])

    [COLOR=darkblue]With[/COLOR] tb
        .Enabled = bEnabled
        .BackColor = IIf(bEnabled, &H80000005, &H8000000F)
        [COLOR=darkblue]If[/COLOR] .Enabled [COLOR=darkblue]Then[/COLOR] .SetFocus
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...

This worked perfectly!!! Thank you so much Domenic!

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
827
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,111
Messages
6,189,007
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