Listbox Validation

[Steve]

Hi

I have a for which has a few textboxs and a list box.

The idea is to fill out the text info and then select a month from the
listbox.

Once done the user selects the submit button and the data is written to a
particular sheet corresponding to the month. So far this all works fine.

However, I want to make sure a month is selected and if not throw up a
message and keep focus on form. The problem is I don't know how to.

What I have now is a message and then the form closes as this is part of the
"Submit" button.

Any help would be appreciated.

Thanks

Steve

 

[Joel]

Do whilke(1)
Mymonth = val(listbox1.value)
if MyMonth >= 1 and MyMonth <= 12 then
exit do
end if

msgbox("Month not valid - Reenter Month")
loop

userform1.hide

 

[Dave Peterson]

I think it's more user-friendly to keep that Ok button disabled until all the
input has been validated.

So if I had a textbox and a listbox on the userform, I could make sure something
was selected in the listbox and the textbox wasn't empty with something like:

Option Explicit
Private Sub CommandButton1_Click()
With Me.ListBox1
MsgBox .ListIndex & vbLf & .Value & vbLf & Me.Textbox1.Value
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox1_Change()
Call ValidateInput
End Sub
Private Sub TextBox1_Change()
Call ValidateInput
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 1 To 12
.AddItem MonthName(Month:=iCtr, abbreviate:=False)
Next iCtr
End With

With Me.CommandButton1
.Caption = "Ok"
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Enabled = True
End With
End Sub
Sub ValidateInput()
Dim OkBtnEnabledChk As Boolean

OkBtnEnabledChk = True

If Me.ListBox1.ListIndex < 1 Then
OkBtnEnabledChk = False
End If

If Me.TextBox1.Value = "" Then
OkBtnEnabledChk = False
End If

Me.CommandButton1.Enabled = OkBtnEnabledChk
End Sub

 

[Steve]

Excellent

Works a treat.

Thanks

I think it's more user-friendly to keep that Ok button disabled until all the
input has been validated.

So if I had a textbox and a listbox on the userform, I could make sure something
was selected in the listbox and the textbox wasn't empty with something like:

Option Explicit
Private Sub CommandButton1_Click()
With Me.ListBox1
MsgBox .ListIndex & vbLf & .Value & vbLf & Me.Textbox1.Value
End With
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub ListBox1_Change()
Call ValidateInput
End Sub
Private Sub TextBox1_Change()
Call ValidateInput
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ListBox1
For iCtr = 1 To 12
.AddItem MonthName(Month:=iCtr, abbreviate:=False)
Next iCtr
End With

With Me.CommandButton1
.Caption = "Ok"
.Enabled = False
End With

With Me.CommandButton2
.Caption = "Cancel"
.Enabled = True
End With
End Sub
Sub ValidateInput()
Dim OkBtnEnabledChk As Boolean

OkBtnEnabledChk = True

If Me.ListBox1.ListIndex < 1 Then
OkBtnEnabledChk = False
End If

If Me.TextBox1.Value = "" Then
OkBtnEnabledChk = False
End If

Me.CommandButton1.Enabled = OkBtnEnabledChk
End Sub

 

[Dave Peterson]

You may want to add a label to your userform.

Then if you find anything wrong in the validation, you can plop a meaningful
warning into that label. I like this idea better than using a msgbox.