Solved

OptionButton  in  Excel VBA

Posted on 2011-02-26
10
1,466 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
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 29

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 29

Expert Comment

by:gowflow
ID: 34999761
srikanthv2322,

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

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 29

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 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