Logic Eluding Me (OptionButtons)

R

Rufus Clupea

Board Regular
Joined
Feb 11, 2019
Messages
85
Hi Folks,

I'm trying to get a process involving OptionButtons to work, but the logic is eluding me (been struggling with it on & off for several days). I'm hoping someone here can help/nudge me in the right direction...

On a UserForm, I have a Frame containing several (10) OptionButtons. The intention is that if/when a user selects an OptionButton different than the default choice (#5)—or subsequently changes their choice—they're prompted with a MsgBox informing them, "Changing OptionButton selection may change some Variables. Do you wish to Continue?" (Y/N)

As it stands, the code works for a "Yes" (Continue) response, but I can't seem to get it to revert to the previous choice if the user clicks "No".

Here's the (sample) code. The problem is in/with Sub Choose_OB(row As Long)
Code: 
[FONT=courier new][COLOR=#0000ff]Option Explicit[/COLOR]

[COLOR=#0000ff]Public [/COLOR]Previous_OB [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]Public [/COLOR]Current_OB [COLOR=#0000ff]As Long[/COLOR]

[COLOR=#0000ff]Sub [/COLOR]UserForm_Initialize()

    Current_OB = 5
    Previous_OB = Current_OB
    
    UserForm1.OptionButton5.Value = [COLOR=#0000ff]True[/COLOR]
    
    Highlight_OB 5, 5

[COLOR=#0000ff]End Sub[/COLOR] [COLOR=#008000]'UserForm_Initialize()[/COLOR]

[COLOR=#0000ff]Sub [/COLOR]Choose_OB(row [COLOR=#0000ff]As Long[/COLOR])

    [COLOR=#0000ff]Dim [/COLOR]i [COLOR=#0000ff]As Long[/COLOR]
    
    Current_OB = row
    
    [COLOR=#0000ff]If[/COLOR] MsgBox("Changing OptionButton selection may change some" & Chr(13) & _
           "Variables." & Chr(13) & Chr(13) & _
           "Do you wish to continue?", 68, "Changing Choice") = vbNo [COLOR=#0000ff]Then[/COLOR]

        UserForm1.Controls("OptionButton" & Previous_OB).Value = [COLOR=#0000ff]True[/COLOR]:
        UserForm1.Controls("OptionButton" & Current_OB).SetFocus

   [COLOR=#0000ff] End If[/COLOR]
    
    Current_OB = Previous_OB
    
    Highlight_OB 1, 10
    
[COLOR=#0000ff]End Sub[/COLOR][COLOR=#008000] 'Choose_OB(row As Long)[/COLOR]

[COLOR=#0000ff]Sub [/COLOR]Highlight_OB(Low [COLOR=#0000ff]As Long[/COLOR], High [COLOR=#0000ff]As Long[/COLOR])

    [COLOR=#0000ff]Dim[/COLOR] i [COLOR=#0000ff]As Long[/COLOR]
    
        [COLOR=#0000ff]For[/COLOR] i = Low [COLOR=#0000ff]To[/COLOR] High
            [COLOR=#0000ff]With[/COLOR] UserForm1.Controls("OptionButton" & i)
                [COLOR=#0000ff]If[/COLOR] .Value = [COLOR=#0000ff]True Then[/COLOR]               [COLOR=#008000]'Embolden Choice[/COLOR]
                    .Font.Bold = [COLOR=#0000ff]True[/COLOR]:
                    .ForeColor = &H80000012:
                    .TabStop = [COLOR=#0000ff]True[/COLOR]
                [COLOR=#0000ff]Else[/COLOR]                                      [COLOR=#008000]'Gray Out Everything Else[/COLOR]
                    .Font.Bold = [COLOR=#0000ff]False[/COLOR]:
                    .ForeColor = &H80000011:
                    .TabStop = [COLOR=#0000ff]False[/COLOR]
                [COLOR=#0000ff]End If[/COLOR]
            [COLOR=#0000ff]End With[/COLOR]
        [COLOR=#0000ff]Next [/COLOR]i

[COLOR=#0000ff]End Sub[/COLOR] [COLOR=#008000]'Highlight_OB(Low As Long, High As Long)[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton1_Click()
    Choose_OB 1
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton2_Click()
    Choose_OB 2
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton3_Click()
    Choose_OB 3
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton4_Click()
    Choose_OB 4
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton5_Click()
    Choose_OB 5
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton6_Click()
    Choose_OB 6
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton7_Click()
    Choose_OB 7
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton8_Click()
    Choose_OB 8
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton9_Click()
    Choose_OB 9
[COLOR=#0000ff]End Sub[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] OptionButton10_Click()
    Choose_OB 10
[COLOR=#0000ff]End Sub[/COLOR][/FONT]

THX! :)
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Try this:

Code: 
Public ob1 As Boolean, ob2 As Boolean, ob3 As Boolean, ob4 As Boolean, ob5 As Boolean
Public ob6 As Boolean, ob7 As Boolean, ob8 As Boolean, ob9 As Boolean, ob10 As Boolean
Dim initial As Boolean
'


Sub UserForm_Initialize()
    initial = True
    UserForm1.OptionButton5.Value = True
    ob5 = True
    initial = False
    Call Highlight_OB
End Sub 'UserForm_Initialize()
'
Sub Choose_OB()
    If MsgBox("Changing OptionButton selection may change some" & Chr(13) & _
           "Variables." & Chr(13) & Chr(13) & _
           "Do you wish to continue?", 68, "Changing Choice") = vbNo Then
        
        initial = True
        OptionButton1 = ob1
        OptionButton2 = ob2
        OptionButton3 = ob3
        OptionButton4 = ob4
        OptionButton5 = ob5
        OptionButton6 = ob6
        OptionButton7 = ob7
        OptionButton8 = ob8
        OptionButton9 = ob9
        OptionButton10 = ob10
        initial = False
    Else
        ob1 = OptionButton1
        ob2 = OptionButton2
        ob3 = OptionButton3
        ob4 = OptionButton4
        ob5 = OptionButton5
        ob6 = OptionButton6
        ob7 = OptionButton7
        ob8 = OptionButton8
        ob9 = OptionButton9
        ob10 = OptionButton10
    End If
    Call Highlight_OB
End Sub


Sub Highlight_OB()
    Dim i As Long
    For i = 1 To 10
        With UserForm1.Controls("OptionButton" & i)
            .Font.Bold = .Value
            .TabStop = .Value
            If .Value Then .ForeColor = &H80000012 Else .ForeColor = &H80000011
        End With
    Next i
End Sub 'Highlight_OB


Private Sub OptionButton1_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton2_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton3_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton4_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton5_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton6_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton7_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton8_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton9_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
Private Sub OptionButton10_Click()
    If initial Then Exit Sub
    Choose_OB
End Sub
 
Last edited:
Upvote 0
Hi Dante,

It works... sort of. As with some of my own attempts, if user responds "No" to the MsgBox, the .SetFocus remains on the new choice (likely an easy fix(?)) I'll play with it.

I need to keep the low & high arguments in Highlight_OB, as in my project, there are ~15 Frames of OptionButtons, and I found that to be an easy way to have them function consistently.

I'd also like to keep the event handlers for each Control to one line (there are close to 800 Controls on a 20 tab MultiPage in my project) wherever possible. In the actual project, almost everything except event handlers is located in different modules (I combined them here for brevity/readability.)

It'll take me a while to follow what else you've improved. Not sure why initial was declared differently than the other globals... :confused: (I'll try to figure that out.)

Thanks for the input! :)
 
Upvote 0
Rufus Clupea said:
Hi Dante,

It works... sort of. As with some of my own attempts, if user responds "No" to the MsgBox, the .SetFocus remains on the new choice (likely an easy fix(?)) I'll play with it.

I need to keep the low & high arguments in Highlight_OB, as in my project, there are ~15 Frames of OptionButtons, and I found that to be an easy way to have them function consistently.

I'd also like to keep the event handlers for each Control to one line (there are close to 800 Controls on a 20 tab MultiPage in my project) wherever possible. In the actual project, almost everything except event handlers is located in different modules (I combined them here for brevity/readability.)

It'll take me a while to follow what else you've improved. Not sure why initial was declared differently than the other globals... :confused: (I'll try to figure that out.)

Thanks for the input! :)
Click to expand...

There are several things, I will try to answer ...
dim initial, is global in this module, public can be used in other modules.

only put the focus on the selected ob.

initial must be in each ob event, if you remove it enters a cycle.

you can put all the ob in a class, with that you avoid managing dozens, you will only manage an event.
 
Upvote 0
DanteAmor said:
you can put all the ob in a class, with that you avoid managing dozens, you will only manage an event
Click to expand...

Yes, I tried that with the example you provided in a previous thread. Unfortunately, my experience/knowledge level is still a bit... rudimentary. :oops: :confused:
(Working on it!)

Part of the problem (for me) is breaking up the Controls after going through the "generic" event handler, sending them to wherever they have to go, and following what I'm doing—even with profuse commenting. The way I'm trying to do it—separate handlers for each control—is much easier for me to follow at my skill level. Later on I may rewrite it, but I think I have to get it functioning correctly first (yes, I know that's backwards from current programming theory/practice). I tried to do a top-down outline of this project, but it got rather unwieldy.

Yes, I'm in over my head! (But I'm learning/making progress. :cool: )
 
Upvote 0
Rufus Clupea said:
Yes, I tried that with the example you provided in a previous thread. Unfortunately, my experience/knowledge level is still a bit... rudimentary. :oops: :confused:
(Working on it!)

Part of the problem (for me) is breaking up the Controls after going through the "generic" event handler, sending them to wherever they have to go, and following what I'm doing—even with profuse commenting. The way I'm trying to do it—separate handlers for each control—is much easier for me to follow at my skill level. Later on I may rewrite it, but I think I have to get it functioning correctly first (yes, I know that's backwards from current programming theory/practice). I tried to do a top-down outline of this project, but it got rather unwieldy.

Yes, I'm in over my head! (But I'm learning/making progress. :cool: )
Click to expand...


Try this, I changed the variables.

Code: 
Dim initial As Boolean
Dim before As Long
'


Sub UserForm_Initialize()
    initial = True
    UserForm1.OptionButton5.Value = True
    before = 5
    initial = False
    Call Highlight_OB
End Sub 'UserForm_Initialize()
'
Sub Choose_OB(n As Long)
    If MsgBox("Changing OptionButton selection may change some" & Chr(13) & _
           "Variables." & Chr(13) & Chr(13) & "Do you wish to continue?", 68, "Changing Choice") = vbNo Then
        initial = True
        Me.Controls("OptionButton" & before) = Me.Controls("OptionButton" & n)
        Me.Controls("OptionButton" & before).SetFocus
        initial = False
    Else
        before = n
    End If
    Call Highlight_OB
End Sub


Sub Highlight_OB()
    Dim i As Long
    For i = 1 To 10
        With UserForm1.Controls("OptionButton" & i)
            .Font.Bold = .Value
            .TabStop = .Value
            If .Value Then .ForeColor = &H80000012 Else .ForeColor = &H80000011
        End With
    Next i
End Sub 'Highlight_OB


Private Sub OptionButton1_Click()
    If initial Then Exit Sub
    Call Choose_OB(1)
End Sub
Private Sub OptionButton2_Click()
    If initial Then Exit Sub
    Call Choose_OB(2)
End Sub
Private Sub OptionButton3_Click()
    If initial Then Exit Sub
    Call Choose_OB(3)
End Sub
Private Sub OptionButton4_Click()
    If initial Then Exit Sub
    Call Choose_OB(4)
End Sub
Private Sub OptionButton5_Click()
    If initial Then Exit Sub
    Call Choose_OB(5)
End Sub
Private Sub OptionButton6_Click()
    If initial Then Exit Sub
    Call Choose_OB(6)
End Sub
Private Sub OptionButton7_Click()
    If initial Then Exit Sub
    Call Choose_OB(7)
End Sub
Private Sub OptionButton8_Click()
    If initial Then Exit Sub
    Call Choose_OB(8)
End Sub
Private Sub OptionButton9_Click()
    If initial Then Exit Sub
    Call Choose_OB(9)
End Sub
Private Sub OptionButton10_Click()
    If initial Then Exit Sub
    Call Choose_OB(10)
End Sub
 
Upvote 0
Go the gift class. It works for all optionbutton 1 or hundreds.

In Class
Code: 
Public WithEvents obCustom1 As MSForms.OptionButton 'custom optionbutton
Private Sub obCustom1_Click()
    If [COLOR=#ff0000]UserForm1[/COLOR].initial Then Exit Sub
    Call [COLOR=#ff0000]UserForm1[/COLOR].Choose_OB(Replace(obCustom1.Name, "OptionButton", ""))
End Sub

In a Module

Code: 
[B][COLOR=#0000ff]Public initial As Boolean[/COLOR][/B]
Dim before As Long
Dim colobs As Collection 'Collection Of Custom optionbutton
'
Private Sub UserForm_Initialize()
'
    Dim ctlLoop As MSForms.Control
    Dim clsObj1 As Class1
    '
    'set optionbutton5 default
[COLOR=#0000ff][B]    initial = True[/B][/COLOR]
        For i = 1 To 10
            Me.Controls("OptionButton" & i).ForeColor = &H80000011
        Next i
        OptionButton5.Value = True
        OptionButton5.SetFocus
        OptionButton5.ForeColor = &H80000012
        OptionButton5.Font.Bold = True
        before = 5
[B][COLOR=#0000ff]    initial = False[/COLOR][/B]
[COLOR=#0000ff]    [/COLOR]
    '[B]load all optionbutton[/B]
    Set colobs = New Collection
    For Each ctlLoop In Me.Controls
        If TypeOf ctlLoop Is MSForms.OptionButton Then
            Set clsObj1 = New Class1
            Set clsObj1.obCustom1 = ctlLoop
            colobs.Add clsObj1
        End If
    Next ctlLoop
End Sub
'
Sub Choose_OB(n As Long)
    If MsgBox("Changing OptionButton selection may change some" & Chr(13) & "Variables." & _
        Chr(13) & Chr(13) & "Do you wish to continue?", 68, "Changing Choice") = vbNo Then
[B][COLOR=#0000ff]        initial = True[/COLOR][/B]
            Me.Controls("OptionButton" & before) = Me.Controls("OptionButton" & n)
            Me.Controls("OptionButton" & before).SetFocus
[B][COLOR=#0000ff]        initial = False[/COLOR][/B]
    Else
        Me.Controls("OptionButton" & n).Font.Bold = True
        Me.Controls("OptionButton" & n).TabStop = True
        Me.Controls("OptionButton" & n).ForeColor = &H80000012
            
        Me.Controls("OptionButton" & before).Font.Bold = False
        Me.Controls("OptionButton" & before).TabStop = False
        Me.Controls("OptionButton" & before).ForeColor = &H80000011


[COLOR=#800000][B]        before = n[/B][/COLOR]
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
242
NoSparks
N
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
395
AndyEd
A
A
  • Question
possible calculation in listbox for debit , credit when search based on textbox ?
Replies
0
Views
285
abdelfattah
A
A
  • Question
Run-time error '380' Could not set the Value property. Invalid property value
Replies
7
Views
253
aabrazil1
A
A
  • Question
Autofilter code based on username
Replies
1
Views
170
aabrazil1
A

Forum statistics

Threads
1,214,870
Messages
6,122,019
Members
449,060
Latest member
LinusJE

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