Require selection in list box.

RJTools

New Member
Joined
Sep 14, 2006
Messages
43
I would like to have the user be required to make a selection from a list box. Can you help me with the following? It works for a TextBox.

Thanks much for your help!

'check for ListBox1
If Trim(Me.ListBox1 .Value) = "" Then
Me.ListBox1 .SetFocus
MsgBox "Please make a selection form the list."
Exit Sub
End If
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Under what conditions [Events]?
Can the user select another cell before the Message, Can the user by-pass the message?

Will the user not be able to change anything before the list-Box selection is made, and this is the first thing that must be done to then do other things?

Is this UserForm code or for a Sheet Control?
 

RJTools

New Member
Joined
Sep 14, 2006
Messages
43
This is a user form. I have a several text boxes and one list box. All the form needs to do is gather information from the user and drop this information into another spreadsheet. All works well except that I would like the user to not be able to submit with out all the required information. I'm fine with the text boxes but I can't get the list box to be a required field.

Thanks for your help!
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
You add this as the first part of the code that is run when the user clicks the submit button on your form!


Dim myLB1

myLB1 = ListBox1.Value

If myLB1 = -1 Then
MsgBox "Please make a selection form the list."
Exit Sub
End If

'Your other button code here!
 

RJTools

New Member
Joined
Sep 14, 2006
Messages
43

ADVERTISEMENT

Thanks for the help but I don't know what I'm doing wrong. Everything I tried I still get an "Invalid use of Null" error code.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

when no selection is made the listindex returns -1
so your code can check this way
Code:
If ListBox1.ListIndex = -1 Then
MsgBox "Please make a selection form the list."
Exit Sub
End If
kind regards,
Erik
 

RJTools

New Member
Joined
Sep 14, 2006
Messages
43

ADVERTISEMENT

That's it! You guys are wonderful! Thanks for helping us novices!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're WELCOME :)

you made me think about that NULLerror

this should work too:
Code:
If IsNull(ListBox1.Value) Then
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Good catch Erik, I like the Null test.

If IsNull(ListBox1.Value) Then

I think I will use that one myself.
 

Forum statistics

Threads
1,140,913
Messages
5,703,134
Members
421,277
Latest member
abudgen

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
Top