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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,091
Messages
5,545,906
Members
410,711
Latest member
Josh324
Top