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

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

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

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

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

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