Listbox Validation

  • Thread starter Thread starter Steve
  • Start date Start date
S

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
 
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
 
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
 
Excellent

Works a treat.

Thanks

Dave Peterson said:
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
Click to expand...
 
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.
 
Back
Top