UserForm Validation Message

[Kevin R]

I have a UserForm to collect some information. When the user clicks OK, I
want it to check to see if certain fields contain a value. If not, return a
message with what needs to be completed and then back to the userform. I
have the following code working (which probably isn't the easiest or cleanest
way of doing it). I want to eliminate the extra ">" lines in the message box
when their is a value . Now it looks like -

To


From
etc...

I want to eliminate those empty lines in the message. Here's my code now.

Dim strToVal, strOffVal, strPosVal, strFromVal, strTitleVal, strSubjVal As
String

If optStandard = True Then

If txtTo = "" Then
strToVal = "To"
End If
If cboFrom = "" Then
strFromVal = "From"
End If
If txtTitle = "" Then
strTitleVal = "Title"
End If
If txtSubject = "" Then
strSubjVal = "Subject"
End If

If boxRecipient = True Then
If cboOffice = "" Then
strOffVal = "Office"
End If
If cboPosition = "" Then
strPosVal = "Position"
End If
End If

If strToVal <> "" Or strFromVal <> "" Or strTitleVal <> "" Or
strSubjVal <> "" Or strOffVal <> "" Or strPosVal <> "" Then
MsgBox ("Please make a selection for the following fields:" &
Chr(13) & Chr(13) _
& " >" & strToVal & Chr(13) _
& " >" & strOffVal & Chr(13) _
& " >" & strPosVal & Chr(13) _
& " >" & strFromVal & Chr(13) _
& " >" & strTitleVal & Chr(13) _
& " >" & strSubjVal)
End If

End If

 

[Gordon Bentley-Mix]

Kevin,

Assuming a UserForm called 'UserForm1' and a CommandButton called 'btnOK'
I'd probably do something like this:

Option Explicit
Dim boolToOK As Boolean
Dim boolFromOK As Boolean
Dim boolTitleOK As Boolean
Dim boolSubjectOK As Boolean
Dim boolOfficeOK As Boolean
Dim boolPositionOK As Boolean

Private Sub btnOK_Click()
If optStandard.Value = True Then
boolToOK = fcnCheckTextBoxValue(txtTo.Value)
boolFromOK = fcnCheckComboBoxValue(cboFrom.ListIndex)
boolTitleOK = fcnCheckTextBoxValue(txtTitle.Value)
boolSubjectOK = fcnCheckTextBoxValue(txtSubject.Value)
If boxRecipient.Value = True Then
boolOfficeOK = fcnCheckComboBoxValue(cboOffice.ListIndex)
boolPositionOK = fcnCheckComboBoxValue(cboPosition.ListIndex)
Else
boolOfficeOK = True
boolPositionOK = True
End If
If boolToOK = False Or boolFromOK = False Or boolTitleOK = False _
Or boolSubjectOK = False Or boolOfficeOK = False Or boolPositionOK =
False Then
MsgBox fcnBuildMessage
Else
UserForm1.Hide
'Do whatever else needs to be done with the values from the
UserForm
Unload UserForm1
End If
Else
UserForm1.Hide
'Do whatever else needs to be done with the values from the UserForm
Unload UserForm1
End If
End Sub

Private Function fcnCheckTextBoxValue(InputValue As String) As Boolean
If InputValue <> "" Then fcnCheckTextBoxValue = True Else
fcnCheckTextBoxValue = False
End Function

Private Function fcnCheckComboBoxValue(InputValue As Integer) As Boolean
If InputValue > 0 Then fcnCheckComboBoxValue = True Else
fcnCheckComboBoxValue = False
End Function

Private Function fcnBuildMessage() As String
Dim strMessage As String
strMessage = "Please make a selection for the following fields:" & vbCr
If boolToOK = False Then strMessage = strMessage & vbCr & " > Office"
If boolFromOK = False Then strMessage = strMessage & vbCr & " > From"
If boolTitleOK = False Then strMessage = strMessage & vbCr & " > Title"
If boolSubjectOK = False Then strMessage = strMessage & vbCr & " >
Subject"
If boolOfficeOK = False Then strMessage = strMessage & vbCr & " >
Office"
If boolPositionOK = False Then strMessage = strMessage & vbCr & " >
Position"
fcnBuildMessage = strMessage
End Function

(I'd also have a CommandButton called 'btnCancel' with Click event code like
this:

Private Sub btnCancel_Click()
UserForm1.Hide
ActiveDocument.Close wdDoNotSaveChanges
End Sub

And I'm sure you probably do too.)

HOWEVER...

Being a "fence at the top of the cliff" kind of guy, I'd probably approach
this differently. I'd put this code into a procedure that checks whether or
not to enable the OK button and call this procedure on the Change events of
every control. And I'd also put some sort of indicator on my UserForm to show
which fields are required - a red label or something.

Finally, I'd make sure I used good coding practices (as I have here), such as:
* using Dim statements for every variable (note that in your Dim statement,
only 'strSubjVal' is declared as a String; the rest are Variants)
* making explicit reference to the .Value property of TextBoxes (your code
relies on .Value being the default property for TextBoxes, which can be
risky);
* using the .ListIndex property of ComboBoxes and making explicit reference
to it (again, your code relies on the default .Value property, which, in
addition to being risky, is not necessarily the best indicator that a value
has been selected);
* making the code modular to avoid "code clutter" created from trying to
put everything into one procedure;
* and using functions that accept arguments to simplify repetitive tasks.

And I'd extend these good practices to doing things like:
* creating specific instances of objects like documents and UserForms and
referring to these objects explicitly (I've made reference to 'UserForm1' and
'ActiveDocument' here but wouldn't do that in a "production" template);
* separating the code that does things with the document from the code that
does things with the UserForm
* commenting my code so I'd know what I was trying to do when I looked at
it again in 6 months - or even 6 hours ;-P

Let me know if you have any questions about any of this.

--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Kevin,

In case you're interested in the "fence at the top" solution, here is some
example code:

Option Explicit

Private Sub UserForm_Initialize()
LoadFromList
LoadOfficeList
LoadPositionList
optStandard.Value = True
CheckOffice
CheckPosition
txtTo.SetFocus
End Sub

Private Sub LoadFromList()
With cboFrom
.AddItem "[SELECT]", 0
.AddItem "From 1", 1
.AddItem "From 2", 2
.ListIndex = 0
End With
End Sub

Private Sub LoadOfficeList()
With cboOffice
.AddItem "[SELECT]", 0
.AddItem "Office 1", 1
.AddItem "Office 2", 2
End With
End Sub

Private Sub LoadPositionList()
With cboPosition
.AddItem "[SELECT]", 0
.AddItem "Position 1", 1
.AddItem "Position 2", 2
End With
End Sub

Private Sub optStandard_Change()
CheckOKButton
End Sub

Private Sub txtTo_Change()
CheckOKButton
End Sub

Private Sub cboFrom_Change()
CheckOKButton
End Sub

Private Sub txtTitle_Change()
CheckOKButton
End Sub

Private Sub txtSubject_Change()
CheckOKButton
End Sub

Private Sub boxRecipient_Change()
CheckOffice
CheckPosition
CheckOKButton
End Sub

Private Sub CheckOffice()
If boxRecipient.Value = True Then EnableOffice Else DisableOffice
End Sub

Private Sub EnableOffice()
With cboOffice
.Enabled = True
.Locked = False
.TabStop = False
.BackColor = &H80000005
.ListIndex = 0
End With
End Sub

Private Sub DisableOffice()
With cboOffice
.Enabled = False
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.ListIndex = -1
End With
End Sub

Private Sub CheckPosition()
If boxRecipient.Value = True Then EnablePosition Else DisablePosition
End Sub

Private Sub EnablePosition()
With cboPosition
.Enabled = True
.Locked = False
.TabStop = False
.BackColor = &H80000005
.ListIndex = 0
End With
End Sub

Private Sub DisablePosition()
With cboPosition
.Enabled = False
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.ListIndex = -1
End With
End Sub

Private Sub cboOffice_Change()
CheckOKButton
End Sub

Private Sub cboPosition_Change()
CheckOKButton
End Sub

Private Sub CheckOKButton()
Dim boolToOK As Boolean
Dim boolFromOK As Boolean
Dim boolTitleOK As Boolean
Dim boolSubjectOK As Boolean
Dim boolOfficeOK As Boolean
Dim boolPositionOK As Boolean
If optStandard.Value = True Then
boolToOK = fcnCheckTextBoxValue(txtTo.Value)
boolFromOK = fcnCheckComboBoxValue(cboFrom.ListIndex)
boolTitleOK = fcnCheckTextBoxValue(txtTitle.Value)
boolSubjectOK = fcnCheckTextBoxValue(txtSubject.Value)
If boxRecipient.Value = True Then
boolOfficeOK = fcnCheckComboBoxValue(cboOffice.ListIndex)
boolPositionOK = fcnCheckComboBoxValue(cboPosition.ListIndex)
Else
boolOfficeOK = True
boolPositionOK = True
End If
Else
boolToOK = True
boolFromOK = True
boolTitleOK = True
boolSubjectOK = True
boolOfficeOK = True
boolPositionOK = True
End If
If boolToOK = True And boolFromOK = True And boolTitleOK = True _
And boolSubjectOK = True And boolOfficeOK = True And boolPositionOK =
True _
Then EnableOKButton Else DisableOKButton
End Sub

Private Sub EnableOKButton()
btnCancel.Default = False
With btnOK
.Enabled = True
.Locked = False
.TabStop = True
.Default = True
End With
End Sub

Private Sub DisableOKButton()
With btnOK
.Enabled = False
.Locked = True
.TabStop = False
.Default = False
End With
btnCancel.Default = False
End Sub

Private Function fcnCheckTextBoxValue(InputValue As String) As Boolean
If InputValue <> "" Then fcnCheckTextBoxValue = True Else
fcnCheckTextBoxValue = False
End Function

Private Function fcnCheckComboBoxValue(InputValue As Integer) As Boolean
If InputValue > 0 Then fcnCheckComboBoxValue = True Else
fcnCheckComboBoxValue = False
End Function

Private Sub btnOK_Click()
UserForm1.Hide
'Do whatever else needs to be done with the values from the UserForm
Unload UserForm1
End Sub

Private Sub btnCancel_Click()
Dim myResult As Integer
myResult = MsgBox("Are you sure you want to cancel?", vbYesNo, "Cancel
Doc")
If myResult = 6 Then
UserForm1.Hide
ActiveDocument.Close wdDoNotSaveChanges
End If
End Sub

A bit more meat on these bones with things like:
* UserForm initialisation code for loading the ComboBox lists
* procedures for enabling / disabling controls
* a safety net on the 'Cancel' button Click event

There should be plenty here to help you on your way.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Oops! The line

btnCancel.Default = False

in the DisableOKButton procedure should be

btnCancel.Default = True

Want to make the 'Cancel' button the default if the 'OK' button is disabled.
Sorry about that.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

Kevin,

In case you're interested in the "fence at the top" solution, here is some
example code:

Option Explicit

Private Sub UserForm_Initialize()
LoadFromList
LoadOfficeList
LoadPositionList
optStandard.Value = True
CheckOffice
CheckPosition
txtTo.SetFocus
End Sub

Private Sub LoadFromList()
With cboFrom
.AddItem "[SELECT]", 0
.AddItem "From 1", 1
.AddItem "From 2", 2
.ListIndex = 0
End With
End Sub

Private Sub LoadOfficeList()
With cboOffice
.AddItem "[SELECT]", 0
.AddItem "Office 1", 1
.AddItem "Office 2", 2
End With
End Sub

Private Sub LoadPositionList()
With cboPosition
.AddItem "[SELECT]", 0
.AddItem "Position 1", 1
.AddItem "Position 2", 2
End With
End Sub

Private Sub optStandard_Change()
CheckOKButton
End Sub

Private Sub txtTo_Change()
CheckOKButton
End Sub

Private Sub cboFrom_Change()
CheckOKButton
End Sub

Private Sub txtTitle_Change()
CheckOKButton
End Sub

Private Sub txtSubject_Change()
CheckOKButton
End Sub

Private Sub boxRecipient_Change()
CheckOffice
CheckPosition
CheckOKButton
End Sub

Private Sub CheckOffice()
If boxRecipient.Value = True Then EnableOffice Else DisableOffice
End Sub

Private Sub EnableOffice()
With cboOffice
.Enabled = True
.Locked = False
.TabStop = False
.BackColor = &H80000005
.ListIndex = 0
End With
End Sub

Private Sub DisableOffice()
With cboOffice
.Enabled = False
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.ListIndex = -1
End With
End Sub

Private Sub CheckPosition()
If boxRecipient.Value = True Then EnablePosition Else DisablePosition
End Sub

Private Sub EnablePosition()
With cboPosition
.Enabled = True
.Locked = False
.TabStop = False
.BackColor = &H80000005
.ListIndex = 0
End With
End Sub

Private Sub DisablePosition()
With cboPosition
.Enabled = False
.Locked = True
.TabStop = False
.BackColor = &H8000000F
.ListIndex = -1
End With
End Sub

Private Sub cboOffice_Change()
CheckOKButton
End Sub

Private Sub cboPosition_Change()
CheckOKButton
End Sub

Private Sub CheckOKButton()
Dim boolToOK As Boolean
Dim boolFromOK As Boolean
Dim boolTitleOK As Boolean
Dim boolSubjectOK As Boolean
Dim boolOfficeOK As Boolean
Dim boolPositionOK As Boolean
If optStandard.Value = True Then
boolToOK = fcnCheckTextBoxValue(txtTo.Value)
boolFromOK = fcnCheckComboBoxValue(cboFrom.ListIndex)
boolTitleOK = fcnCheckTextBoxValue(txtTitle.Value)
boolSubjectOK = fcnCheckTextBoxValue(txtSubject.Value)
If boxRecipient.Value = True Then
boolOfficeOK = fcnCheckComboBoxValue(cboOffice.ListIndex)
boolPositionOK = fcnCheckComboBoxValue(cboPosition.ListIndex)
Else
boolOfficeOK = True
boolPositionOK = True
End If
Else
boolToOK = True
boolFromOK = True
boolTitleOK = True
boolSubjectOK = True
boolOfficeOK = True
boolPositionOK = True
End If
If boolToOK = True And boolFromOK = True And boolTitleOK = True _
And boolSubjectOK = True And boolOfficeOK = True And boolPositionOK =
True _
Then EnableOKButton Else DisableOKButton
End Sub

Private Sub EnableOKButton()
btnCancel.Default = False
With btnOK
.Enabled = True
.Locked = False
.TabStop = True
.Default = True
End With
End Sub

Private Sub DisableOKButton()
With btnOK
.Enabled = False
.Locked = True
.TabStop = False
.Default = False
End With
btnCancel.Default = False
End Sub

Private Function fcnCheckTextBoxValue(InputValue As String) As Boolean
If InputValue <> "" Then fcnCheckTextBoxValue = True Else
fcnCheckTextBoxValue = False
End Function

Private Function fcnCheckComboBoxValue(InputValue As Integer) As Boolean
If InputValue > 0 Then fcnCheckComboBoxValue = True Else
fcnCheckComboBoxValue = False
End Function

Private Sub btnOK_Click()
UserForm1.Hide
'Do whatever else needs to be done with the values from the UserForm
Unload UserForm1
End Sub

Private Sub btnCancel_Click()
Dim myResult As Integer
myResult = MsgBox("Are you sure you want to cancel?", vbYesNo, "Cancel
Doc")
If myResult = 6 Then
UserForm1.Hide
ActiveDocument.Close wdDoNotSaveChanges
End If
End Sub

A bit more meat on these bones with things like:
* UserForm initialisation code for loading the ComboBox lists
* procedures for enabling / disabling controls
* a safety net on the 'Cancel' button Click event

There should be plenty here to help you on your way.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

Kevin,

Assuming a UserForm called 'UserForm1' and a CommandButton called 'btnOK'
I'd probably do something like this:

Option Explicit
Dim boolToOK As Boolean
Dim boolFromOK As Boolean
Dim boolTitleOK As Boolean
Dim boolSubjectOK As Boolean
Dim boolOfficeOK As Boolean
Dim boolPositionOK As Boolean

Private Sub btnOK_Click()
If optStandard.Value = True Then
boolToOK = fcnCheckTextBoxValue(txtTo.Value)
boolFromOK = fcnCheckComboBoxValue(cboFrom.ListIndex)
boolTitleOK = fcnCheckTextBoxValue(txtTitle.Value)
boolSubjectOK = fcnCheckTextBoxValue(txtSubject.Value)
If boxRecipient.Value = True Then
boolOfficeOK = fcnCheckComboBoxValue(cboOffice.ListIndex)
boolPositionOK = fcnCheckComboBoxValue(cboPosition.ListIndex)
Else
boolOfficeOK = True
boolPositionOK = True
End If
If boolToOK = False Or boolFromOK = False Or boolTitleOK = False _
Or boolSubjectOK = False Or boolOfficeOK = False Or boolPositionOK =
False Then
MsgBox fcnBuildMessage
Else
UserForm1.Hide
'Do whatever else needs to be done with the values from the
UserForm
Unload UserForm1
End If
Else
UserForm1.Hide
'Do whatever else needs to be done with the values from the UserForm
Unload UserForm1
End If
End Sub

Private Function fcnCheckTextBoxValue(InputValue As String) As Boolean
If InputValue <> "" Then fcnCheckTextBoxValue = True Else
fcnCheckTextBoxValue = False
End Function

Private Function fcnCheckComboBoxValue(InputValue As Integer) As Boolean
If InputValue > 0 Then fcnCheckComboBoxValue = True Else
fcnCheckComboBoxValue = False
End Function

Private Function fcnBuildMessage() As String
Dim strMessage As String
strMessage = "Please make a selection for the following fields:" & vbCr
If boolToOK = False Then strMessage = strMessage & vbCr & " > Office"
If boolFromOK = False Then strMessage = strMessage & vbCr & " > From"
If boolTitleOK = False Then strMessage = strMessage & vbCr & " > Title"
If boolSubjectOK = False Then strMessage = strMessage & vbCr & " >
Subject"
If boolOfficeOK = False Then strMessage = strMessage & vbCr & " >
Office"
If boolPositionOK = False Then strMessage = strMessage & vbCr & " >
Position"
fcnBuildMessage = strMessage
End Function

(I'd also have a CommandButton called 'btnCancel' with Click event code like
this:

Private Sub btnCancel_Click()
UserForm1.Hide
ActiveDocument.Close wdDoNotSaveChanges
End Sub

And I'm sure you probably do too.)

HOWEVER...

Being a "fence at the top of the cliff" kind of guy, I'd probably approach

 

[Gordon Bentley-Mix]

I'm having one of _those_ days today...

In the fcnBuildMessage function, the line:

If boolToOK = False Then strMessage = strMessage & vbCr & " > Office"

should be

If boolToOK = False Then strMessage = strMessage & vbCr & " > To"

so the user will know that it's the 'To' field that's missing.

I *swear* I tested this! ;-D
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Jean-Guy Marcil]

I have a UserForm to collect some information. When the user clicks OK, I
want it to check to see if certain fields contain a value. If not, return a
message with what needs to be completed and then back to the userform. I
have the following code working (which probably isn't the easiest or cleanest
way of doing it). I want to eliminate the extra ">" lines in the message box
when their is a value . Now it looks like -


I want to eliminate those empty lines in the message. Here's my code now.

Dim strToVal, strOffVal, strPosVal, strFromVal, strTitleVal, strSubjVal As
String

If optStandard = True Then

If txtTo = "" Then
strToVal = "To"
End If
If cboFrom = "" Then
strFromVal = "From"
End If
If txtTitle = "" Then
strTitleVal = "Title"
End If
If txtSubject = "" Then
strSubjVal = "Subject"
End If

If boxRecipient = True Then
If cboOffice = "" Then
strOffVal = "Office"
End If
If cboPosition = "" Then
strPosVal = "Position"
End If
End If

If strToVal <> "" Or strFromVal <> "" Or strTitleVal <> "" Or
strSubjVal <> "" Or strOffVal <> "" Or strPosVal <> "" Then
MsgBox ("Please make a selection for the following fields:" &
Chr(13) & Chr(13) _
& " >" & strToVal & Chr(13) _
& " >" & strOffVal & Chr(13) _
& " >" & strPosVal & Chr(13) _
& " >" & strFromVal & Chr(13) _
& " >" & strTitleVal & Chr(13) _
& " >" & strSubjVal)
End If

End If

The esiest is to include everything with the the string creation, as in:

If txtTo = "" Then
strToVal = " >To" & Chr(13)
Else
strToVal = ""
End If

Then, use something like:

MsgBox ("Please make a selection for the following fields:" &
Chr(13) & Chr(13) _
& strToVal & strOffVal & strPosVal & strFromVal _
& strTitleVal & strSubjVal)

Without testing any of your code, I believe it would work.

Also, since you posted your code, I just thought I'd let you know that your
variable declaration is a bit off..:

Dim strToVal, strOffVal, strPosVal, strFromVal, strTitleVal,
strSubjVal As String

means that strSubjVal is a String, but all others are Variant.

It is definitely a good idea to type the variable when you declare them, but
in VBA you must type each one individually:

Dim strToVal As String, strOffVal As String, strPosVal As String,
strFromVal As String, strTitleValAs String, strSubjVal As String

or (I prefer the following because it is just easier to see what is going
on, to my mind anyway...)

Dim strToVal As String
Dim strOffVal As String
Dim strPosVal As String
Dim strFromVal As String
Dim strTitleValAs String
Dim strSubjVal As String

etc.

 

[Gordon Bentley-Mix]

Very elegant, Jean-Guy, and probably easier for Kevin to understand than my
total rework of his code - although I'm still partial to disabling the OK
button over displaying an error message (and I think this is the approach
that the Windows Interface Guidelines would recommend as well).

One change I would make is to put the Chr(13) before the text, as in:

If txtTo.Value = "" Then
strToVal = Chr(13) & " >To"
Else
strToVal = ""
End If

This way you don't end up with a blank line at the end.

And I'd remove one Chr(13) from the "instructional" portion of the message:

MsgBox ("Please make a selection for the following fields:" & Chr(13) ...

(I'd also specify the property to be evaluated explicitly; e.g. .Value.)

However, I absolutely agree with your recommendations on declaring the
variable types, and (as you can see from my code) I prefer using a Dim
statement for each variable as well - apparently my mind works a bit like
yours. ;-P
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

:
[snip]

 

[Jean-Guy Marcil]

Very elegant, Jean-Guy, and probably easier for Kevin to understand than my
total rework of his code - although I'm still partial to disabling the OK
button over displaying an error message (and I think this is the approach
that the Windows Interface Guidelines would recommend as well).

Based on some of the atrocious UI designs I have seen over the years from
MSFT, I am not sure thay can be used as a "Standard"!

As a user, I think a disabled "OK" button is OK, as long as a message (a
label could be used) on the userform displays why it is disabled... I would
hate not being able to hit "OK" and not knowing what I must do in order to
enable it...

Good point re. the Chr(13).