ListBox data validation
 

Hi there,

I have a userform with a listbox. I need to verify that the user has
selected an item from this listbox, and if not have a message appear to tell
them to do so.

I've written the following code:

'check for a Month/Year
If Me.ListBox2.Value = "" Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

This code will work on a Textbox, but not a listbox. Any help would be
greatly appreciated!

JJ

ListBox data validation
 

If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

--
Regards,
Tom Ogilvy


"jjordan" wrote:

Hi there,

I have a userform with a listbox. I need to verify that the user has
selected an item from this listbox, and if not have a message appear to tell
them to do so.

I've written the following code:

'check for a Month/Year
If Me.ListBox2.Value = "" Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

This code will work on a Textbox, but not a listbox. Any help would be
greatly appreciated!

JJ

ListBox data validation
 

Hi Tom,

Thanks for your reply, however this will still allow the form data to
populate the spreadsheet without an error msg appearing. I'm sure it's
something I'm overlooking here. I realize this is probably too much info,
but here is my code for the listbox, and the code to save the form data to
the spreadsheet:

Private Sub UserForm_Initialize()
With ListBox2
.AddItem ""
.AddItem "April 2007"
.AddItem "May 2007"
.AddItem "June 2007"
.AddItem "July 2007"
.AddItem "August 2007"
.AddItem "September 2007"
.AddItem "October 2007"
.AddItem "November 2007"
.AddItem "December 2007"
.AddItem "January 2008"
.AddItem "February 2008"
.AddItem "March 2008"

End With
ListBox2.ListIndex = 0
End Sub

'save data and unload form data
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MonthlyData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Month/Year
If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ListBox2.Value
ws.Cells(iRow, 2).Value = Me.ListBox1.Value
ws.Cells(iRow, 3).Value = Me.TextBox2.Value
ws.Cells(iRow, 4).Value = Me.TextBox3.Value

'clear the data
Me.ListBox2.ListIndex = 0
Me.ListBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.CommandButton2.SetFocus

End Sub

Hope this helps!

JJ

"Tom Ogilvy" wrote:

If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

--
Regards,
Tom Ogilvy


"jjordan" wrote:

Hi there,

I have a userform with a listbox. I need to verify that the user has
selected an item from this listbox, and if not have a message appear to tell
them to do so.

I've written the following code:

'check for a Month/Year
If Me.ListBox2.Value = "" Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

This code will work on a Textbox, but not a listbox. Any help would be
greatly appreciated!

JJ

ListBox data validation
 

I showed you how to check the listbox to see if a selection has been made.

How you implement the check is up to you.

Your code is writing the data. If you don't want to write the data, make
the check before you write the data.

--
Regards,
Tom Ogilvy


"jjordan" wrote:

Hi Tom,

Thanks for your reply, however this will still allow the form data to
populate the spreadsheet without an error msg appearing. I'm sure it's
something I'm overlooking here. I realize this is probably too much info,
but here is my code for the listbox, and the code to save the form data to
the spreadsheet:

Private Sub UserForm_Initialize()
With ListBox2
.AddItem ""
.AddItem "April 2007"
.AddItem "May 2007"
.AddItem "June 2007"
.AddItem "July 2007"
.AddItem "August 2007"
.AddItem "September 2007"
.AddItem "October 2007"
.AddItem "November 2007"
.AddItem "December 2007"
.AddItem "January 2008"
.AddItem "February 2008"
.AddItem "March 2008"

End With
ListBox2.ListIndex = 0
End Sub

'save data and unload form data
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MonthlyData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Month/Year
If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ListBox2.Value
ws.Cells(iRow, 2).Value = Me.ListBox1.Value
ws.Cells(iRow, 3).Value = Me.TextBox2.Value
ws.Cells(iRow, 4).Value = Me.TextBox3.Value

'clear the data
Me.ListBox2.ListIndex = 0
Me.ListBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.CommandButton2.SetFocus

End Sub

Hope this helps!

JJ

"Tom Ogilvy" wrote:

If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

--
Regards,
Tom Ogilvy


"jjordan" wrote:

Hi there,

I have a userform with a listbox. I need to verify that the user has
selected an item from this listbox, and if not have a message appear to tell
them to do so.

I've written the following code:

'check for a Month/Year
If Me.ListBox2.Value = "" Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

This code will work on a Textbox, but not a listbox. Any help would be
greatly appreciated!

JJ

ListBox data validation
 

I sorry Tom, I quite new at this game!

I did change my code to:
'check for a Month/Year
If Me.ListBox2.ListIndex = 0 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

And this worked because I have a "blank" for the first item in my list with
the listindex of 0.

What I am wondering now is would there be a better place to put this code to
ensure the user has filled out the form completely prior to the "save" button
on the form?

Thanks,

Janice

"Tom Ogilvy" wrote:

I showed you how to check the listbox to see if a selection has been made.

How you implement the check is up to you.

Your code is writing the data. If you don't want to write the data, make
the check before you write the data.

--
Regards,
Tom Ogilvy


"jjordan" wrote:

Hi Tom,

Thanks for your reply, however this will still allow the form data to
populate the spreadsheet without an error msg appearing. I'm sure it's
something I'm overlooking here. I realize this is probably too much info,
but here is my code for the listbox, and the code to save the form data to
the spreadsheet:

Private Sub UserForm_Initialize()
With ListBox2
.AddItem ""
.AddItem "April 2007"
.AddItem "May 2007"
.AddItem "June 2007"
.AddItem "July 2007"
.AddItem "August 2007"
.AddItem "September 2007"
.AddItem "October 2007"
.AddItem "November 2007"
.AddItem "December 2007"
.AddItem "January 2008"
.AddItem "February 2008"
.AddItem "March 2008"

End With
ListBox2.ListIndex = 0
End Sub

'save data and unload form data
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MonthlyData")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Month/Year
If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ListBox2.Value
ws.Cells(iRow, 2).Value = Me.ListBox1.Value
ws.Cells(iRow, 3).Value = Me.TextBox2.Value
ws.Cells(iRow, 4).Value = Me.TextBox3.Value

'clear the data
Me.ListBox2.ListIndex = 0
Me.ListBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.CommandButton2.SetFocus

End Sub

Hope this helps!

JJ

"Tom Ogilvy" wrote:

If Me.ListBox2.ListIndex = -1 Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

--
Regards,
Tom Ogilvy


"jjordan" wrote:

Hi there,

I have a userform with a listbox. I need to verify that the user has
selected an item from this listbox, and if not have a message appear to tell
them to do so.

I've written the following code:

'check for a Month/Year
If Me.ListBox2.Value = "" Then
msg = "Please enter Valid Date"
MsgBox msg
UserForm1.ListBox2.SetFocus
Exit Sub
End If

This code will work on a Textbox, but not a listbox. Any help would be
greatly appreciated!

JJ

ListBox data validation
 

And this worked because I have a "blank" for the first item in my
list with the listindex of 0.

My question to you is why do you have a "blank" item in your ListBox at all?
What does having that accomplish for you? I ask because I can't think of any
reason where doing this makes sense. Think about all the commercial and/or
professional programs you have used... do you ever remember seeing a "blank"
item in a ListBox (or ComboBox for that matter)?

Rick

ListBox data validation
 

Darn, you had to ask that question! :)

I'm embarrassed to admit that being a newbie to this game this was a
work-around, as I did not have time to find out the correct way to achieve
the look I wanted.

As you mentioned about referring to any program having a blank item at the
top of a list, yes, it does appear that way to a user - being that when a
form is opened, all fields appear blank and ready for them to enter
something. When the form I created opened the first item in my list appeared
in the field, so to achieve the look I wanted I added the "blank" item to the
top of my list.

I am creating this spreadsheet for an inept computer user (and I mean that
in the nicest possible way!) and I am attempting to make it as fool-proof as
possible. I can imagine this user opening a form, and pressing the enter key
with the intent to move to the first field, and instead accepting the first
name in the list without even noticing it, and proceeding to complete the
remainder of the form.

Do I get any points for creativity? :)

But, now that I have your attention Rick, is there a correct way to do this?
I would really appreciate your feedback.

Thanks,

Janice

"Rick Rothstein (MVP - VB)" wrote:

And this worked because I have a "blank" for the first item in my
list with the listindex of 0.

My question to you is why do you have a "blank" item in your ListBox at all?
What does having that accomplish for you? I ask because I can't think of any
reason where doing this makes sense. Think about all the commercial and/or
professional programs you have used... do you ever remember seeing a "blank"
item in a ListBox (or ComboBox for that matter)?

Rick

ListBox data validation
 

Darn, you had to ask that question! :)

LOL

But, now that I have your attention Rick, is there a correct way to do
this?
I would really appreciate your feedback.

Remove the "blank" item and set the ListIndex property of the ListBox to -1.
The -1 means no item is selected (hence, none is highlighted). Tom already
gave you the processing method in his first post... test the ListIndex
property for -1... if it is -1, that means no item is currently selected.
The user cannot create this condition (the -1 value for the ListIndex) once
he/she selects something in the list, so this testing method is foolproof.
However, you set the ListIndex property to -1 in code anytime you need want
to in order to present the user with a "new" (unselected) ListBox.

Rick

ListBox data validation
 

Call me thick - I must be missing something here!

I thought perhaps I wasn't understanding Tom's message because it didn't
work the way I had expected.

I have now removed my "blank" item from my list, set my listbox2.listindex =
-1, confirmed that my "TopIndex" in the properties window is at -1. When I
open the form, the top item in my list still appears. No, it is not
highlighted, and yes, the verification works, however I wanted the field to
appear blank.

I know the computer is smarter than me - what could I be doing to cause this?

JJ

"Rick Rothstein (MVP - VB)" wrote:

Darn, you had to ask that question! :)

LOL

But, now that I have your attention Rick, is there a correct way to do
this?
I would really appreciate your feedback.

Remove the "blank" item and set the ListIndex property of the ListBox to -1.
The -1 means no item is selected (hence, none is highlighted). Tom already
gave you the processing method in his first post... test the ListIndex
property for -1... if it is -1, that means no item is currently selected.
The user cannot create this condition (the -1 value for the ListIndex) once
he/she selects something in the list, so this testing method is foolproof.
However, you set the ListIndex property to -1 in code anytime you need want
to in order to present the user with a "new" (unselected) ListBox.

Rick

ListBox data validation
 

I have now removed my "blank" item from my list, set my listbox2.listindex
=
-1, confirmed that my "TopIndex" in the properties window is at -1. When
I
open the form, the top item in my list still appears. No, it is not
highlighted, and yes, the verification works, however I wanted the field
to
appear blank.

What "field" are you talking about that isn't blank?

Rick

ListBox data validation
 

The "field" I am referring to is the listbox on my userform. I have it only
large enough to show the first item (which I want to be blank). Then, when
the user moves to that field (listbox) she will start typing and the item
from the list will appear (based on her keystrokes).

I'm sorry if I'm not making myself clear, as I said before I am new to this
game and trying to be succinct :(

JJ

"Rick Rothstein (MVP - VB)" wrote:

I have now removed my "blank" item from my list, set my listbox2.listindex
=
-1, confirmed that my "TopIndex" in the properties window is at -1. When
I
open the form, the top item in my list still appears. No, it is not
highlighted, and yes, the verification works, however I wanted the field
to
appear blank.

What "field" are you talking about that isn't blank?

Rick

ListBox data validation
 

The "field" I am referring to is the listbox on my userform. I have it
only
large enough to show the first item (which I want to be blank). Then,
when
the user moves to that field (listbox) she will start typing and the item
from the list will appear (based on her keystrokes).

Just out of curiosity, why not use a ComboBox instead of a Listbox? They are
only one line high, setting their ListIndex property to -1 blanks them and
the user gets the benefit of being able to select from a list via the
drop-down arrow.

Rick

ListBox data validation
 

From my understanding, a user can actually add more items to the list using a
ComboBox. Are you going to tell me I'm wrong and there was a whole lot easier
way to go about this ?!?!?!

(Trust me, I will be taking a course in the fall to fully understand this)

JJ


"Rick Rothstein (MVP - VB)" wrote:

The "field" I am referring to is the listbox on my userform. I have it
only
large enough to show the first item (which I want to be blank). Then,
when
the user moves to that field (listbox) she will start typing and the item
from the list will appear (based on her keystrokes).

Just out of curiosity, why not use a ComboBox instead of a Listbox? They are
only one line high, setting their ListIndex property to -1 blanks them and
the user gets the benefit of being able to select from a list via the
drop-down arrow.

Rick

ListBox data validation
 

From my understanding, a user can actually add more items to the list
using a
ComboBox.

Not the case. If the Style property of the ComboBox is 0 =
fmStyleDropDownCombo, the user can type in a value that does not appear in
the list, but it isn't actually added to the list of values. If the Style
property is 2 = fmStyleDropDownList, the user may only select items from the
list. He can't type in a new value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"jjordan" wrote in message
...
From my understanding, a user can actually add more items to the list
using a
ComboBox. Are you going to tell me I'm wrong and there was a whole lot
easier
way to go about this ?!?!?!

(Trust me, I will be taking a course in the fall to fully understand this)

JJ


"Rick Rothstein (MVP - VB)" wrote:

The "field" I am referring to is the listbox on my userform. I have it
only
large enough to show the first item (which I want to be blank). Then,
when
the user moves to that field (listbox) she will start typing and the
item
from the list will appear (based on her keystrokes).

Just out of curiosity, why not use a ComboBox instead of a Listbox? They
are
only one line high, setting their ListIndex property to -1 blanks them
and
the user gets the benefit of being able to select from a list via the
drop-down arrow.

Rick

ListBox data validation
 

From my understanding, a user can actually add more items to the list
using a
ComboBox. Are you going to tell me I'm wrong and there was a whole lot
easier
way to go about this ?!?!?!

Sorry, but you are wrong. See Chip's posting for more details on this.

(Trust me, I will be taking a course in the fall to fully understand this)

Taking a course is a great idea... it will give you a structured
introduction and overview that will provide you with a foundation on which
you can build. One key piece of advice that I give to newcomers in VB...
read the documentation... read the help files on each function, statement
and control (along with its properties) available to you in VB(A). Now, I am
not suggesting that you memorize them all (heaven forbid, I have only a
small percentage of them memorized myself); rather, I am suggesting that you
learn they are there and roughly what they are capable of doing. If you know
they exist, and you know what they can do, you can think in terms of them
when you do your coding. You don't have to know the syntax cold (the help
files or VB's Intellisense can fill those in for you); all you are trying to
do is KNOW what is available to you as a VBA programmer, nothing more. Hell,
I even found a use for the Partition statement once and I was only able to
do that because I knew it existed and roughly what it did. I can hear the
majority of people following this thread going: "The Partition statement,
what the hell is that?!!?" It is not really a very useful command (but, as I
said, I did find a use for it once) and I would be willing to bet 99.9% of
the VB programmers (both for VBA and compiled VB) don't even know it exists.
Anyway, that is my one piece of wisdom concerning programming... take it or
leave it.

There is a decent reference book out there (it may be hard to find with all
the VB.NET stuff eating up the bookstore space) that you may want to
consider picking up. It is called

"VB & VBA in a Nutshell" by Paul Lomax
Published by O'Reilly
ISBN No. 1-56592-358-8

Best would be if you could check it out in a local bookstore to see it the
layout and style is to your liking.

Rick

ListBox data validation
 

Tom, Rick & Chip,

You guys are awesome! My form now looks and acts the way I want it to!

Goes to show you how a little knowledge can be dangerous - or at least a
huge hindrance!

Chip, I did notice your website on your signature and took a quick boo this
morning - looks like a great resource and I will definitely be going back!
Hot Chilies eh?

Thanks a bunch all of you! I picked up a lot of info here, both from what
you suggested I try, and then I stretched that info a little further in other
areas to learn even more!

As I said, I will be taking some training and hope that one day I'll be able
to help someone in return!

Thanks,

Janice

"Chip Pearson" wrote:

From my understanding, a user can actually add more items to the list
using a
ComboBox.

Not the case. If the Style property of the ComboBox is 0 =
fmStyleDropDownCombo, the user can type in a value that does not appear in
the list, but it isn't actually added to the list of values. If the Style
property is 2 = fmStyleDropDownList, the user may only select items from the
list. He can't type in a new value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"jjordan" wrote in message
...
From my understanding, a user can actually add more items to the list
using a
ComboBox. Are you going to tell me I'm wrong and there was a whole lot
easier
way to go about this ?!?!?!

(Trust me, I will be taking a course in the fall to fully understand this)

JJ


"Rick Rothstein (MVP - VB)" wrote:

The "field" I am referring to is the listbox on my userform. I have it
only
large enough to show the first item (which I want to be blank). Then,
when
the user moves to that field (listbox) she will start typing and the
item
from the list will appear (based on her keystrokes).

Just out of curiosity, why not use a ComboBox instead of a Listbox? They
are
only one line high, setting their ListIndex property to -1 blanks them
and
the user gets the benefit of being able to select from a list via the
drop-down arrow.

Rick

ListBox data validation
 

Rick, first off, Thank you for your patience with me!
And, thank you for the advice - I will try to track the book down that you
mention to carry me over the summer. I too am a strong believer that you
only need to know something exists to be able to use it in future - how to do
it comes when you use it, and if you use it enough it will eventually sink in
:)

I have completed an on-line course, however without a paper reference I
found it very frustrating trying to go back through the videos (chapters) to
find the info again to reference it, even though I thought I had taken very
good notes. Between the two other books I've purchased and read, and then
this user group (both current and historical info) and of course the websites
you folks refer to here I think what I did was bombard myself with too much
info and not able to index it in my little mind. I believe a structured
course will help to align things for me and then maybe I will be able to use
it!

I will say, I've had a blast with this little project I've worked on, and
really look forward to torturing myself again in the very near future!

Thanks for all your help!

Janice

"Rick Rothstein (MVP - VB)" wrote:

From my understanding, a user can actually add more items to the list
using a
ComboBox. Are you going to tell me I'm wrong and there was a whole lot
easier
way to go about this ?!?!?!

Sorry, but you are wrong. See Chip's posting for more details on this.

(Trust me, I will be taking a course in the fall to fully understand this)

Taking a course is a great idea... it will give you a structured
introduction and overview that will provide you with a foundation on which
you can build. One key piece of advice that I give to newcomers in VB...
read the documentation... read the help files on each function, statement
and control (along with its properties) available to you in VB(A). Now, I am
not suggesting that you memorize them all (heaven forbid, I have only a
small percentage of them memorized myself); rather, I am suggesting that you
learn they are there and roughly what they are capable of doing. If you know
they exist, and you know what they can do, you can think in terms of them
when you do your coding. You don't have to know the syntax cold (the help
files or VB's Intellisense can fill those in for you); all you are trying to
do is KNOW what is available to you as a VBA programmer, nothing more. Hell,
I even found a use for the Partition statement once and I was only able to
do that because I knew it existed and roughly what it did. I can hear the
majority of people following this thread going: "The Partition statement,
what the hell is that?!!?" It is not really a very useful command (but, as I
said, I did find a use for it once) and I would be willing to bet 99.9% of
the VB programmers (both for VBA and compiled VB) don't even know it exists.
Anyway, that is my one piece of wisdom concerning programming... take it or
leave it.

There is a decent reference book out there (it may be hard to find with all
the VB.NET stuff eating up the bookstore space) that you may want to
consider picking up. It is called

"VB & VBA in a Nutshell" by Paul Lomax
Published by O'Reilly
ISBN No. 1-56592-358-8

Best would be if you could check it out in a local bookstore to see it the
layout and style is to your liking.

Rick