Solved

OptionButton  in  Excel VBA

Posted on 2011-02-26
10
1,446 Views
Last Modified: 2012-05-11
I have four OptionButtons in my UserForm, if the user doesn't select any one of them, then an message should pop up saying 'You should select any one of the above'

I have placed all the Optionbuttons in a Frame and named the frame
0
Comment
Question by:srikanthv2322
  • 4
  • 3
10 Comments
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
Normally you would use a If Then Routine to process code for the four different options, the last Else being the no select message (ie if none of the four paths was triggered)

Alternativey you could run a simple check when the user presses a commandbutton to launch an action, as below

Cheers

Dave

Private Sub CommandButton1_Click()
    If (Me.OptionButton1.Value + Me.OptionButton2.Value + Me.OptionButton3.Value + Me.OptionButton4.Value) = 0 Then
        MsgBox "You must select a value before proceeding!"
    End If
End Sub

Open in new window

0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
Comment Utility
full if logic with sample file

Cheers

Dave
Private Sub CommandButton1_Click()
    If Me.OptionButton1 Then
        MsgBox "You pressed 1"
    ElseIf Me.OptionButton2 Then
        MsgBox "You pressed 2"
    ElseIf Me.OptionButton3 Then
        MsgBox "You pressed 3"
    ElseIf Me.OptionButton4 Then
        MsgBox "You pressed 4"
    Else
        MsgBox "You must select a value before proceeding!"
    End If
End Sub

Open in new window

ob.xlsm
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
Comment Utility
Well practically Brettdj gave you a solution based on the limited info you supplied as you didn't mention beside the 4 options buttons and the fram what you have on the form.

An other way to handle this would be to put the routine where you practically need the user to act. I would suggest basis the very limited info you supplied is the following:

In the UserForm_Initialize() event you put
Frame1.setfocus

You give focus to the Frame1 upon loading your form.

and in the Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean) event of the Frame you put the routine to check if the user have selected an option button or not

If Me.Optionbutton1.value = 0 and Me.OptionButton2.value = 0 and Me.Optionbutton3.value = 0 and Me.OptionButton4.value = 0 then
    Msgbox("Please Select an Option")
    Cancel = True  
endif

This way whatever the user do if he doesn't choose an option he can't leave the frame's focus.

Rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
srikanthv2322,

Did you have a chance to try the suggested solution ?
Rgds/gowflow
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Hi broomee9,

No doubt that brettdj was the first one to give the solution however based on a commanbutton that the user may want to create or not. On the other hand, I provided a solution without any creation of an extra control and basis also the limited info provided by the asker. As far as I am concerned I do not object to a split of points case needed.

Hope this info enough to clarify the judgment case the asker does not interveen.

gowflow
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility

> On the other hand, I provided a solution without any creation of an extra control and basis also the limited info provided by the asker.

Two comments

(1) It is a given that the UserFrom would have a commandbutton to let the user proceed/exit
(2) I provided a full working example in 34990313 which went beyond the scope of the question, ie it also handled what option was checked, not just a "no-check". Which presumably makes the solution more useful

So as the
-  first respondent,
- with a more complete solution
-  including a worked example

 I suggest that EE etiqueete dicttates that a Modertaor split if any should be guided by my input. As such

a#34990313.html 400 points brettdj
a#34990600.html 100 points golfow (for use of the cancel event)

I will abide by any Mod/ZA ruling

Cheers

Dave
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry I am not here to beg for points. You may give all the points to Brettdj.

As comments

1) Do not agree with the assumption that a userform MUST have a command button !!! I have been developping software for over 30 years and can give you examples not using commandboxes. Actually my solution is used without a commandbutton !

2)  I provided a full working example in 34990313 which went beyond the scope of the question,
Correct but it is not a MUST as the initial question was:
>>>  if the user doesn't select any one of them, then an message should pop up saying 'You should select any one of the above'
and My solution answered 'EXPLICITLY' the request of the asker.

sorry but had to make this clarification as indeed my original post was mis-interpreted.
gowflow
0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now