Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Validation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Validation
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 "Steve" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Validation
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Validation
Excellent
Works a treat. Thanks "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox Validation
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. Steve wrote: Excellent Works a treat. Thanks "Dave Peterson" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ARRG! Names not showing up, can't use in validation or ListBox??! | Excel Programming | |||
Removing Data Validation (Listbox) | Excel Worksheet Functions | |||
ListBox data validation | Excel Programming | |||
Data Validation Listbox problem | Excel Programming | |||
Change event for data validation listbox | Excel Programming |