Solved

OptionButton  in  Excel VBA

Posted on 2011-02-26
10
1,517 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34990304
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
ID: 34990313
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 31

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 34990600
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 31

Expert Comment

by:gowflow
ID: 34999761
srikanthv2322,

Did you have a chance to try the suggested solution ?
Rgds/gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 35191529
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
ID: 35194565

> 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 31

Expert Comment

by:gowflow
ID: 35197011
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
ID: 35356946
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

622 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