How to force a user to select an option button?

[Rainmanne]

I've got a model with a code that should depend on the selection of one of two option buttons. When a user opens the model, the both option buttons are unselected. I need to make sure that the user selects one of them before the main code is run. So if nothing is selected, then it should display a message box saying something like "Please select an option" and stop the rest of the code from executing.

I guess it should be an IF statement using OptionButton.Value = False as a condition, but I cannot figure out how to do it. It should be something like

Sub optionbuttonselect()
If ActiveSheet.OptionButtons("Option Button 2").Value = False Then
If ActiveSheet.OptionButtons("Option Button 4").Value = False Then
MsgBox "Check the options"
    End If
    End If
End Sub

But it obviously does not work.

 

[rlv01]

Why not set one of the Optionbuttons as the default instead of making the user always choose?

 

[rlv01]

One way is to use the worksheet event code.

Private Sub Worksheet_Activate()
    CheckOptions
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CheckOptions
End Sub

Private Sub CheckOptions()
    If Not (Me.OptionButton2 Or Me.OptionButton4) Then
        MsgBox "Please select an option"
    End If
End Sub

 

[Rainmanne]

Why not set one of the Optionbuttons as the default instead of making the user always choose?

I need a user to select one of the options. Otherwise, they might forget to change the option from the default.

One way is to use the worksheet event code.

Private Sub Worksheet_Activate()
    CheckOptions
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CheckOptions
End Sub

Private Sub CheckOptions()
    If Not (Me.OptionButton2 Or Me.OptionButton4) Then
        MsgBox "Please select an option"
    End If
End Sub

Thanks a lot. Is it possible to integrate it into the larger macro? A user usually hits the button "Start the Model", which initiates the code.

 

[rlv01]

It's possible. Just include a call to the macro CheckOptions in the larger macro. But what are going to do if they ignore the message box and don't select an option? You have no enforcement mechanism.

 

[Rainmanne]

It's possible. Just include a call to the macro CheckOptions in the larger macro. But what are going to do if they ignore the message box and don't select an option? You have no enforcement mechanism.

Oh, I mean I need to make sure that the code does not run without the selection. So it should be he message and the abort of the code.

 

[Rainmanne]

Not sure how I can call a private sub from a public sub...

 

[Rainmanne]

I was actually thinking about something like:

If OptionButton1 is checked then
Go ahead
If not check OptionButton2
If OptionButton2 if checked
Go ahead
If not, abort the code and show the message "Select an option"
End If

It looks like a simple structure but I cannot figure out how to do it and if it works.

 

[dmt32]

It looks like a simple structure but I cannot figure out how to do it and if it works.

Try making a function that returns a boolean value that you can use in your main code to check if a selection has been made

Place in STANDARD module

Function HasBeenSelected() As Boolean
    Dim i As Long
    For i = 1 To 2
        HasBeenSelected = ActiveSheet.OptionButtons("Option Button " & Choose(i, 2, 4)).Value = xlOn
        If HasBeenSelected Then Exit For
    Next i
    If Not HasBeenSelected Then MsgBox "Please Select An Option", 48, "Selection Required"
End Function

To call it, place following as first line of code in your main procedure

Sub MyProcedure()
    If Not HasBeenSelected Then Exit Sub
  
    'rest of my code
End Sub

Hope Helpful


Dave

 

[Rainmanne]

To call it, place following as first line of code in your main procedure

Sub MyProcedure()
    If Not HasBeenSelected Then Exit Sub
 
    'rest of my code
End Sub

Thanks a lot. I just cannot figure out where to put End If if it's needed. The macro complains about it whereever I put it I tried to put it straight after the statement, and I've got

Compile error: End if without block if

If I put it at the end of the macro it complains at the end of the code.