UserForms, mandatory completion of fields

[Zani]

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?

 

[Myles]

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

 

[Zani]

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?!?!

 

[Myles]

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

 

[Zani]

Many thanks for all your help, I am well on my way now!

 

[GB]

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]

Thats very helpful thank you, I am still currently working on this so I can
tidying my code up nicely now, thanks again.

Zani

 

[Dave Peterson]

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]

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!)

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

 

[Dave Peterson]

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

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]

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!)

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

 

[mikeg710]

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

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

 

[Dave Peterson]

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 "".

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