VBA ~ Input masks for a created User Form? Is this possible

[MarianneR]

Hi!

I'm a novice at VBA. I've created a User Form for people who are not experienced with Excel and I'm having a couple of problems.

1) The form includes text boxes as well as pull-downs (combo boxes). I was wondering if there was a way to use input masks (like in Access) to prevent uses from entering inappropriate data. For example, I have several date fields. Is it possible to have my user form already have a mm/dd/yyyy format for them? Or, if that's not possible, is there some way to make the User Form beep at them? I have prompts in the labels next to the boxes, but I'm afraid it won't be enough.

2) I'm also having problems with errors that my form is producing. If you click outside of the form boundaries or hit enter in some cells, there's an error with my Ok to quit statement:

Private Sub UserForm_Click()

Private OKToQuit As Boolean ' determines if button pressed to exit

End Sub

Private Sub Cancel_Click()
OKToQuit = True
Me.Hide
End Sub

Private Sub Apply_Click()
MsgBox "Button pressed; Apply choices"
OKToQuit = True
Me.Hide
End Sub

3) As I said, I've got combo boxes, but they let you type in them. Is there a way to make this NOT happen?

4) I don't understand the use of If statements in the code. For example, I have a particular yes/no question. If the user enters YES, I'd like them to enter a date in the next box. If either NO is entered or there's no information, I'd like to have that text box turned off. Is this possible?

Again, I'm really just learningthis, so your patience is appreciated. Or, I'm happy to share this with someone if that's easier ~ please leave me your email address.

Thanks!
Marianne

 

[Andrew Poulsom]

You have to create your own masks for TextBox entry. Here is one for date in the format dd/mm/yy that you may be able to adapt:

Private Sub TextBox1_Change()
    Dim Char As String
    Dim x As Date
    Dim y As Date
    Char = Right(TextBox1.Text, 1)
    Select Case Len(TextBox1.Text)
    Case 1 To 2, 4 To 5, 7 To 8
        If Char Like "#" Then
            If Len(TextBox1) = 8 Then
                On Error Resume Next
                x = DateValue(TextBox1.Text)
                y = DateSerial(Right(TextBox1, 2), Mid(TextBox1, 4, 2), Left(TextBox1, 2))
                If Err = 0 And x = y Then
                    On Error GoTo 0
                    Exit Sub
                Else
                    Err.Clear
                    On Error GoTo 0
                    TextBox1.SelStart = 0
                    TextBox1.SelLength = Len(TextBox1.Text)
                    MsgBox "Please enter a valid date in the form dd/mm/yy", vbCritical + vbOKOnly, "Error"
                    Exit Sub
                End If
            Else
                Exit Sub
            End If
        End If
    Case 3, 6
        If Char Like "/" Then Exit Sub
    End Select
    Beep
    On Error Resume Next
    TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)
    TextBox1.SelStart = Len(TextBox1.Text)
End Sub

I don't understand why you are using the UserForm_Click event procedure and the purpose of your OKToQuit variable.

To prevent the user from typing anything in a Combobox's Edit box (other than what's in the list) set the Style property to 2 - fmStyleDrpDownList.

For Yes/No type questions use a CheckBox, and use it's Click event to test if its Value property is True (checked) or False (unchecked). You can set the Enabled property of a TextBox to turn it on (True) or off (False). Or you could use its Visible property to Hide (False) or show it (True).

 

[Joe Was]

An easy way to mask is to build your form as composit fields:


mm/dd/ccyy

"2-digit TextBox for mm" "Label for /" "2-digit TextBox for dd" "Label for /" "4-digit TextBox for ccyy"

then in your code combing each of the three values into one Variable and work with the variable!

 

[MarianneR]

Thank you both very much. I will try these suggestions.

Again, your patience is really appreciated.

Marianne

 

[MarianneR]

Two more questions.

1) If I wanted a four-digit year, would I change the code in these ways?

Sub txtBirth_Change()
Dim Char As String
Dim x As Date
Dim y As Date
Char = Right(txtBirth.Text, 1)
Select Case Len(txtBirth.Text)
Case 1 To 2, 4 To 5, 7 To 10
If Char Like "#" Then
If Len(txtBirth) = 10 Then
On Error Resume Next
x = DateValue(txtBirth.Text)
y = DateSerial(Right(txtBirth, 2), Mid(txtBirth, 4, 2), Left(txtBirth, 4))
If Err = 0 And x = y Then
On Error GoTo 0
Exit Sub
Else
Err.Clear
On Error GoTo 0
txtBirth.SelStart = 0
txtBirth.SelLength = Len(txtBirth.Text)
MsgBox "Please enter a valid date in the form dd/mm/yyyy", vbCritical + vbOKOnly, "Error"
Exit Sub
End If
Else
Exit Sub
End If
End If
Case 3, 6
If Char Like "/" Then Exit Sub
End Select
Beep
On Error Resume Next
txtBirth.Text = Left(txtBirth.Text, Len(txtBirth.Text) - 1)
txtBirth.SelStart = Len(txtBirth.Text)

txt.Birth is my combo box for birthdate.

2) The error beeps and says "Please enter a valid date in the form" no matter what date I enter. But, if the date is in the right format, it will take the information after beeping.



Thanks!

 

[MarianneR]

Last question (sorry).

As I mentioned before, I have a Yes or No question that I'm using a combo box for. I realize the checkbox would have been better, but I couldn't figure out how to make them work. Anyway, if the user selects yes for this question, I'd like the next question to be an option. If the user selects No or doesn't answer, I'd like the question to not be eligible.

For example, my form might ask the following questions:
Was there a follow-up? (combo box Yes/No)
Date of follow-up: (text box formatted for date, only eligible if Yes was selected above)

Is this possible? Thanks!!

 

[Greg Truby]

Just answering the last one -- assumes you do use a checkbox and that the default for ENABLED on the textbox is set to FALSE during userform design...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> chkFollowUp_Change()
    Me.tbxFollowUpDate.Enabled = Me.chkFollowUp
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Andrew Poulsom]

DateSerial is YMD so:

y = DateSerial(Right(txtBirth, 2), Mid(txtBirth, 4, 2), Left(txtBirth, 4))

should be:

y = DateSerial(Right(txtBirth, 4), Mid(txtBirth, 4, 2), Left(txtBirth, 2))

 

[MarianneR]

Thanks again. I really appreciate your patience and your help!

Unfortunately, the darned thing is still beeping at me even when I enter things in a correct date format. Hmmm... "Please enter a valid date in the form dd/mm/yyyy"

Private Sub txtBirth_Change()
Dim Char As String
Dim x As Date
Dim y As Date
Char = Right(txtBirth.Text, 1)
Select Case Len(txtBirth.Text)
Case 1 To 2, 4 To 5, 7 To 10
If Char Like "#" Then
If Len(txtBirth) = 10 Then
On Error Resume Next
x = DateValue(txtBirth.Text)
y = DateSerial(Right(txtBirth, 4), Mid(txtBirth, 4, 2), Left(txtBirth, 2))
If Err = 0 And x = y Then
On Error GoTo 0
Exit Sub
Else
Err.Clear
On Error GoTo 0
txtBirth.SelStart = 0
txtBirth.SelLength = Len(txtBirth.Text)
MsgBox "Please enter a valid date in the form dd/mm/yyyy", vbCritical + vbOKOnly, "Error"
Exit Sub
End If
Else
Exit Sub
End If
End If
Case 3, 6
If Char Like "/" Then Exit Sub
End Select
Beep
On Error Resume Next
txtBirth.Text = Left(txtBirth.Text, Len(txtBirth.Text) - 1)
txtBirth.SelStart = Len(txtBirth.Text)
End Sub

Is the section in red right?

 

[Andrew Poulsom]

Are you really entering a date in the format dd/mm/yyyy and not mm/dd/yyyy? For US dates you will need:

y = DateSerial(Right(txtBirth, 4), Left(txtBirth, 2), Mid(txtBirth, 4, 2))

and of course you will need to change the message in the MsgBox.