Allowing only Date and Integer for user form

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I have a user form and as part of the procedure when I click OK is the
following ....
'Make sure a number is entered
If TextName1 = "" Then
MsgBox "You must enter a number."
Exit Sub
End If
'Transfer the number
If Range("M5") = True Then Range("O5") = TextName1
If TextName2 = "" Then
MsgBox "You must enter a date."
Exit Sub
End If
'Transfer the date
If Range("M5") = True Then Range("P5") = TextName2

1. I would like to make sure that only an integer is entered for textBox1.
2. I would like to make sure that only a date (as per long format) is
entered for textBox2.
3. Hard as I try I can't seem to put any Dim statements in for the 2
variables.
eg. Dim TextName1 as Integer always comes up with an error. (The procedure
runs fine without the Dim statements but as I understand it Dim statements
will speed up calculation time.)

Rob
 
Hi Rob

Numbers are easy. Try this code and it's impossible to type anything but integers into the
box:

Private Sub TextBox1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub

It does not prevent a Ctrl V -paste though. For that use something like:

Private Sub TextBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If Shift = 2 Then KeyCode = 0
End Sub

Dates are more complex. I believe the user should be allowed to enter dates in whichever
format she wants. So evaluate afterwards like this perhaps:

Private Sub CommandButton1_Click()
Dim D As Date
If IsDate(TextBox2.Text) Then
D = DateValue(TextBox2.Text)
MsgBox Format(D, "dddd mmm d.yyyy")
Else
MsgBox "No date"
End If
End Sub
 
Thanks Harald but I'm sorry, I seem to be a complete idiot. Can you please
put those suggestions within my code for me (which I've pasted in below), as
try as I might, I can't seem to get it happening without errors. I wouldn't
worry about using the part that prevents pasting as it would be extremely
unlikely that that would occur for this procedure.
Rob

My code so far is....

Private Sub OKButton_Click()

'Make sure a number is entered
If TextName1 = "" Then
MsgBox "You must enter a number."
Exit Sub
End If
'Transfer the number
If Range("M5") = True Then Range("O5") = TextName1
If Range("M6") = True Then Range("O6") = TextName1
If Range("M7") = True Then Range("O7") = TextName1
If Range("M8") = True Then Range("O8") = TextName1
If Range("M9") = True Then Range("O9") = TextName1
If Range("M10") = True Then Range("O10") = TextName1
If Range("M11") = True Then Range("O11") = TextName1
If Range("M12") = True Then Range("O12") = TextName1
'Make sure a date is entered
If TextName2 = "" Then
MsgBox "You must enter a date."
Exit Sub
End If
'Transfer the date
If Range("M5") = True Then Range("P5") = TextName2
If Range("M6") = True Then Range("P6") = TextName2
If Range("M7") = True Then Range("P7") = TextName2
If Range("M8") = True Then Range("P8") = TextName2
If Range("M9") = True Then Range("P9") = TextName2
If Range("M10") = True Then Range("P10") = TextName2
If Range("M11") = True Then Range("P11") = TextName2
If Range("M12") = True Then Range("P12") = TextName2
'Delete/reset closing balance check to 0
Sheet4.Range("M5:M12") = False
Sheet4.Range("U5:U12") = 0
Sheet4.Range("U14") = 0

Sheet4.ShowAllData
Unload Me
End Sub

----- Original Message -----
From: "Harald Staff" <[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Friday, January 16, 2004 6:09 PM
Subject: Re: Allowing only Date and Integer for user form
 
If your number textbox is named TextName1 then replace that in my first and second code
and then paste it in the userform module -outside your sub- as shown below. It just
prevents non-numeric entries and has nothing to do with your button validation which
should be perfect with this code added.

For the date, your code modified, still assuming your date textbox name is Textname2. I
also moved the validation up, so that nothing happens unless both fields are filled in
correctly. You might not want that, if so move it back.

Private Sub TextName1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub

Private Sub TextName1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If Shift = 2 Then KeyCode = 0
End Sub

Private Sub OKButton_Click()
Dim D As Date
Dim L As Long

'Make sure a number is entered
If TextName1 = "" Then
MsgBox "You must enter a number."
Exit Sub
Else
L = Val(TextName1.Text)
End If
'Make sure a date is entered
If IsDate(TextName2.Text) Then
D = DateValue(TextName2.Text)
Else
MsgBox "You must enter a date."
Exit Sub
End If

'Transfer the number
If Range("M5") = True Then Range("O5") = L
If Range("M6") = True Then Range("O6") = L
If Range("M7") = True Then Range("O7") = L
If Range("M8") = True Then Range("O8") = L
If Range("M9") = True Then Range("O9") = L
If Range("M10") = True Then Range("O10") = L
If Range("M11") = True Then Range("O11") = L
If Range("M12") = True Then Range("O12") = L

'Transfer the date
If Range("M5") = True Then Range("P5") = D
If Range("M6") = True Then Range("P6") = D
If Range("M7") = True Then Range("P7") = D
If Range("M8") = True Then Range("P8") = D
If Range("M9") = True Then Range("P9") = D
If Range("M10") = True Then Range("P10") = D
If Range("M11") = True Then Range("P11") = D
If Range("M12") = True Then Range("P12") = D
'Delete/reset closing balance check to 0
Sheet4.Range("M5:M12") = False
Sheet4.Range("U5:U12") = 0
Sheet4.Range("U14") = 0

Sheet4.ShowAllData
Unload Me
End Sub
 
rob nobel said:
YOWSA! BEWDY!
Thanks for all of that Harald.
Worked first time!
Rob
Click to expand...

Glad to hear that Rob. Thank you for the feedback.

Best wishes Harald
Followup to newsgroup only please.
 
Back
Top