Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
I have a series of userforms collecting user information. I now want to make
some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
I would abandon the Query_Close Event and rather use a CommandButton with this code: Private Sub CommandButton1_Click() If Len(TextBox1).text = 0 Then MsgBox "please complete all mandatory fields" Exit sub Else Unload UserForm1 Userform5.Show End If End Sub -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=500988 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Thanks Myles
This worked perfectly when I trialled it for just the one textbox to check for completion, however when I tried to add in other text boxes I keep getting a Block If without End If statement - any ideas?!?! -- Zani (if I have posted here, I really am stuck!) "Myles" wrote: I would abandon the Query_Close Event and rather use a CommandButton with this code: Private Sub CommandButton1_Click() If Len(TextBox1).text = 0 Then MsgBox "please complete all mandatory fields" Exit sub Else Unload UserForm1 Userform5.Show End If End Sub -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=500988 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Zani, try: Private Sub CommandButton1_Click() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If Len(ctl.Text) = 0 Then MsgBox "please complete all mandatory fields" Exit Sub End If End If Next Unload UserForm1 UserForm2.Show End Sub myles -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=500988 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Many thanks for all your help, I am well on my way now!
-- Zani (if I have posted here, I really am stuck!) "Myles" wrote: Zani, try: Private Sub CommandButton1_Click() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If Len(ctl.Text) = 0 Then MsgBox "please complete all mandatory fields" Exit Sub End If End If Next Unload UserForm1 UserForm2.Show End Sub myles -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=500988 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Realize that the function provided requires ALL fields to be completed.
I think your problem before could have been a number of things, but most likely (and I'm guessing) you didn't *nest* your if statements properly. I'm going to write out what is needed to accomplish the task you want if not ALL fields are mandatory in pseudocode. So please do not try to copy all of the formatting, paste directly in your program, and expect it to function properly. If Text1 is blank then Inform user that Text1 must be completed Elseif Text2 is blank then Inform user that Text2 must be completed Elseif Text3 is blank then Inform user that Text3 must be completed Else Unload UserForm1 UserForm2.Show end if Instead of exiting the sub after each check to prevent moving to the next sub, I used the if else feature so that if it makes it through each of the text tests then it will perform the actions of moving to the next form. This would allow you to do a few different things. If say you wanted to do some additional cleanup after each of the tests that is common to every option, then you could add additional code below the end if line. If that is performed and the same cleanup is not necessary after showing userform2 then just after userform2.show there could be an exit sub. Or exit sub could be added to the end of each test and after userform2 is shown additional work could be performed either specific to userform2 (before the end if) or in general (after the end if). Help any more? "Zani" wrote: Many thanks for all your help, I am well on my way now! -- Zani (if I have posted here, I really am stuck!) "Myles" wrote: Zani, try: Private Sub CommandButton1_Click() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If Len(ctl.Text) = 0 Then MsgBox "please complete all mandatory fields" Exit Sub End If End If Next Unload UserForm1 UserForm2.Show End Sub myles -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=500988 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Thats very helpful thank you, I am still currently working on this so I can
tidying my code up nicely now, thanks again. Zani -- Zani (if I have posted here, I really am stuck!) "GB" wrote: Realize that the function provided requires ALL fields to be completed. I think your problem before could have been a number of things, but most likely (and I'm guessing) you didn't *nest* your if statements properly. I'm going to write out what is needed to accomplish the task you want if not ALL fields are mandatory in pseudocode. So please do not try to copy all of the formatting, paste directly in your program, and expect it to function properly. If Text1 is blank then Inform user that Text1 must be completed Elseif Text2 is blank then Inform user that Text2 must be completed Elseif Text3 is blank then Inform user that Text3 must be completed Else Unload UserForm1 UserForm2.Show end if Instead of exiting the sub after each check to prevent moving to the next sub, I used the if else feature so that if it makes it through each of the text tests then it will perform the actions of moving to the next form. This would allow you to do a few different things. If say you wanted to do some additional cleanup after each of the tests that is common to every option, then you could add additional code below the end if line. If that is performed and the same cleanup is not necessary after showing userform2 then just after userform2.show there could be an exit sub. Or exit sub could be added to the end of each test and after userform2 is shown additional work could be performed either specific to userform2 (before the end if) or in general (after the end if). Help any more? "Zani" wrote: Many thanks for all your help, I am well on my way now! -- Zani (if I have posted here, I really am stuck!) "Myles" wrote: Zani, try: Private Sub CommandButton1_Click() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If Len(ctl.Text) = 0 Then MsgBox "please complete all mandatory fields" Exit Sub End If End If Next Unload UserForm1 UserForm2.Show End Sub myles -- Myles ------------------------------------------------------------------------ Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746 View this thread: http://www.excelforum.com/showthread...hreadid=500988 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Another option would be to keep the ok button disabled until all your fields are
ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Thats great thanks Dave, I assume with some tweaking I can add in the
listboxes and optionbuttons that are also in use and must be completed, though I can see me having trouble with the option buttons, when you only have to determine whether one out of say a group of four has been checked! Thanks again -- Zani (if I have posted here, I really am stuck!) "Dave Peterson" wrote: Another option would be to keep the ok button disabled until all your fields are ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Ah, but one of the good things about option buttons is that you can set a
default (None or 0???) and never have to worry about one of them being selected. But you could do something like: Option Explicit Private Sub CommandButton2_Click() Unload Me End Sub Private Sub OptionButton1_Click() Call CheckAllRules End Sub Private Sub OptionButton2_Click() Call CheckAllRules End Sub Private Sub OptionButton3_Click() Call CheckAllRules End Sub Private Sub OptionButton4_Click() Call CheckAllRules End Sub Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl if oktoenable = false then 'why check, it's already false else If Me.OptionButton1.Value = True _ Or Me.OptionButton2.Value = True _ Or Me.OptionButton3.Value = True _ Or Me.OptionButton4.Value = True Then 'ok, don't change anything Else 'all false OkToEnable = False End If end if Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: Thats great thanks Dave, I assume with some tweaking I can add in the listboxes and optionbuttons that are also in use and must be completed, though I can see me having trouble with the option buttons, when you only have to determine whether one out of say a group of four has been checked! Thanks again -- Zani (if I have posted here, I really am stuck!) "Dave Peterson" wrote: Another option would be to keep the ok button disabled until all your fields are ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Dave you are a wonder and now officially my hero of the day! Thanks for all
your help, it's really very much appreciated and spot on! -- Zani (if I have posted here, I really am stuck!) "Dave Peterson" wrote: Ah, but one of the good things about option buttons is that you can set a default (None or 0???) and never have to worry about one of them being selected. But you could do something like: Option Explicit Private Sub CommandButton2_Click() Unload Me End Sub Private Sub OptionButton1_Click() Call CheckAllRules End Sub Private Sub OptionButton2_Click() Call CheckAllRules End Sub Private Sub OptionButton3_Click() Call CheckAllRules End Sub Private Sub OptionButton4_Click() Call CheckAllRules End Sub Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl if oktoenable = false then 'why check, it's already false else If Me.OptionButton1.Value = True _ Or Me.OptionButton2.Value = True _ Or Me.OptionButton3.Value = True _ Or Me.OptionButton4.Value = True Then 'ok, don't change anything Else 'all false OkToEnable = False End If end if Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: Thats great thanks Dave, I assume with some tweaking I can add in the listboxes and optionbuttons that are also in use and must be completed, though I can see me having trouble with the option buttons, when you only have to determine whether one out of say a group of four has been checked! Thanks again -- Zani (if I have posted here, I really am stuck!) "Dave Peterson" wrote: Another option would be to keep the ok button disabled until all your fields are ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
I am trying to accomplish this task in a user form I created. There are
three text boxes (txtProjectName, txtProjectDescription, txtProcess) and two command buttons (cmdAdd, cmdCancel). I want the cmdAdd button enabled only when the user has successfully entered data into txtProjectName AND txtProjectDescription (required fields). Once the cmdAdd button is clicked, it is disabled again. The cmdAdd button never becomes enabled regardless if text is entered or missing from either of the two required fields. Any thoughts would be greatly appreciated! Here is the code: Option Explicit Private Sub cmdAdd_Click() ' Disable the Add button after clicking it cmdAdd.Enabled = False End Sub Private Sub cmdCancelButton_Click() Unload Me End Sub Private Sub txtProjectDescription_Change() Call CheckRequiredFields End Sub Private Sub txtProjectName_Change() Call CheckRequiredFields End Sub Private Sub UserForm_Initialize() ' Disable the Add button before form displays Me.cmdAdd.Enabled = False End Sub Private Sub CheckRequiredFields() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.cmdAdd.Enabled = OkToEnable End Sub "Dave Peterson" wrote: Another option would be to keep the ok button disabled until all your fields are ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
This code:
OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl is checking all the textboxes on the form--not just the two you want checked. Just replace it with code that looks at those two textboxes: if me.txtprojectname.value = "" _ or me.txtprojectdescription.value = "" then oktoenable = false else oktoenable = true end if And after you do the work in the cmdAdd procedure, disable the button and change at least one (both) of those textboxes to "". mikeg710 wrote: I am trying to accomplish this task in a user form I created. There are three text boxes (txtProjectName, txtProjectDescription, txtProcess) and two command buttons (cmdAdd, cmdCancel). I want the cmdAdd button enabled only when the user has successfully entered data into txtProjectName AND txtProjectDescription (required fields). Once the cmdAdd button is clicked, it is disabled again. The cmdAdd button never becomes enabled regardless if text is entered or missing from either of the two required fields. Any thoughts would be greatly appreciated! Here is the code: Option Explicit Private Sub cmdAdd_Click() ' Disable the Add button after clicking it cmdAdd.Enabled = False End Sub Private Sub cmdCancelButton_Click() Unload Me End Sub Private Sub txtProjectDescription_Change() Call CheckRequiredFields End Sub Private Sub txtProjectName_Change() Call CheckRequiredFields End Sub Private Sub UserForm_Initialize() ' Disable the Add button before form displays Me.cmdAdd.Enabled = False End Sub Private Sub CheckRequiredFields() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.cmdAdd.Enabled = OkToEnable End Sub "Dave Peterson" wrote: Another option would be to keep the ok button disabled until all your fields are ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForms, mandatory completion of fields
Thank you very much Dave!
"Dave Peterson" wrote: This code: OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl is checking all the textboxes on the form--not just the two you want checked. Just replace it with code that looks at those two textboxes: if me.txtprojectname.value = "" _ or me.txtprojectdescription.value = "" then oktoenable = false else oktoenable = true end if And after you do the work in the cmdAdd procedure, disable the button and change at least one (both) of those textboxes to "". mikeg710 wrote: I am trying to accomplish this task in a user form I created. There are three text boxes (txtProjectName, txtProjectDescription, txtProcess) and two command buttons (cmdAdd, cmdCancel). I want the cmdAdd button enabled only when the user has successfully entered data into txtProjectName AND txtProjectDescription (required fields). Once the cmdAdd button is clicked, it is disabled again. The cmdAdd button never becomes enabled regardless if text is entered or missing from either of the two required fields. Any thoughts would be greatly appreciated! Here is the code: Option Explicit Private Sub cmdAdd_Click() ' Disable the Add button after clicking it cmdAdd.Enabled = False End Sub Private Sub cmdCancelButton_Click() Unload Me End Sub Private Sub txtProjectDescription_Change() Call CheckRequiredFields End Sub Private Sub txtProjectName_Change() Call CheckRequiredFields End Sub Private Sub UserForm_Initialize() ' Disable the Add button before form displays Me.cmdAdd.Enabled = False End Sub Private Sub CheckRequiredFields() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.cmdAdd.Enabled = OkToEnable End Sub "Dave Peterson" wrote: Another option would be to keep the ok button disabled until all your fields are ok: Option Explicit Private Sub TextBox1_Change() Call CheckAllRules End Sub Private Sub TextBox2_Change() Call CheckAllRules End Sub Private Sub TextBox4_Change() Call CheckAllRules End Sub Private Sub UserForm_Initialize() Me.CommandButton1.Enabled = False End Sub Private Sub CheckAllRules() Dim myCtrl As Control Dim OkToEnable As Boolean OkToEnable = True For Each myCtrl In Me.Controls If TypeOf myCtrl Is MSForms.TextBox Then If myCtrl.Object.Value = "" Then OkToEnable = False Exit For End If End If Next myCtrl Me.CommandButton1.Enabled = OkToEnable End Sub Zani wrote: I have a series of userforms collecting user information. I now want to make some of the fields on UserForm1 mandatory for completion so I am running the following code: Private Sub Userform_queryclose(Cancel As Integer, CloseMode As Integer) If Len(TextBox1) = 0 Then MsgBox "please complete all mandatory fields" Cancel = True End If End Sub However when a mandatory field is missed in userform1 and this code kicks in, it still shows next Userform that would have been shown had all the information been collected correctly. At the end of Userform1 I have the following: Unload UserForm1 Load UserForm5 UserForm5.Show How do I stop this next userform from appearing until after all the mandatory fields have been completed? -- Zani (if I have posted here, I really am stuck!) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mandatory fields | Excel Worksheet Functions | |||
Excel Spreadsheet - Mandatory fields | Excel Worksheet Functions | |||
UserForms - making responses mandatory | Excel Programming | |||
Creating mandatory fields(cells)... | Excel Worksheet Functions | |||
Can I make cell completion mandatory in excel? | Excel Discussion (Misc queries) |