Excel VBA - Userform Textbox problem

[thesteelmaker]

The code at the bottom gives me a textbox, formatted to 1 dp. Som
vailidation to stop "text" enteries and some validation to give
positive number.

All is ok so far.

On doing some testing, the text box lets me do some strange things
that i do not want, i.e.:

Enter into text box

6.6.6 this changes to 0.3
6 7 changes to 38174.0
5/8/0 changes to 36743.0

I take it the last two are accepted as dates.

why?

These are errors that i dont really want.

Any suggestions would be helpful.

Thanks

Code=================================
Private Sub TextBox1_BeforeUpdate(ByVal Cancel A
MSForms.ReturnBoolean)

' Set number format
TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")

' Check the value entered is numeric
If Not IsNumeric(TextBox1.Value) Then
MsgBox "You entered a non-numeric value, try again."
vbExclamation, "Error"
TextBox1.SetFocus
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
Cancel = True
Else

' Check the value is positive
If (TextBox1.Text < 0) Then
MsgBox "You inserted a negative number, try again."
vbExclamation, "Error"
TextBox1.SetFocus
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
Cancel = True
End If
End If
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
End Su

 

[Harald Staff]

Hi

You don't need all that. If positive decimal numbers are the only entries
allowed then use simply

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

Private Sub TextBox1_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
Select Case KeyAscii
Case 46
If InStr(TextBox1.Text, ".") > 0 Then _
KeyAscii = 0
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub

Now it's impossible to type anything unwanted in there and you don't have to
annoy your users with modal textboxes popping up telling them how stupid
they are. They hate that, and after a while they hate the application
too -trust me on this.

HTH. Best wishes Harald

 

[Dave Peterson]

Just to add to Harald's suggestion:

This portion:

If Shift = 2 Then
If KeyCode = 86 Then KeyCode = 0
End If

Stops ctrl-V (pasting into the textbox).

 

[thesteelmaker]

Thank you very much.

I'm very impressed.

I've not come across any reference to keycodes, or anything in yo
coding.

Could you explain a bit more.

Thanks.

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

Private Sub TextBox1_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
Select Case KeyAscii
Case 46
If InStr(TextBox1.Text, ".") > 0 Then _
KeyAscii = 0
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Su

 

[Dave Peterson]

Try adding this to a test form:

Option Explicit
Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
MsgBox KeyCode
End Sub

And you'll see what each keystroke represents.
(hit ctrl and don't let go. Then hit V and you'll see the 86.)

You can search VBA's help for "keycode constants" and see vbKeyV and how it
represents 86.

 

[Jon Peltier]

Well, isn't this nice. This morning I was wondering how I was going to
tell whether the user typed or pasted his information into the textbox!
Thanks Dave and Harald.

(An additional coincidence is the OP's handle, given that my real
technical training is in metallurgy.)

- Jon

 

[Harald Staff]

Hi Guys

Just adding a few minor things.

Jon: The paste code is pretty raw as you noticed. My real life code for
those events is
- cancel
- read the clipboard content
- if acceptable then paste it in, or paste the "acceptable part" in
but it's pretty large and confusing, so I added the cancel part only for
demonstration.

Steelmaker: Many built-in events provide variables that it's code can
validate and change.For example in ThisWorkbook:

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

Now this workbook will never save because we cancel it. Not useful as is,
but add a condition or two to see if the save location is ok, if this and
that is completely filled out, ...

TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
and "A" is the same key, number 65, the diference is Shift, which is 0 for a
and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
usually meaning that the entry is finished so go to next field or click the
OK button or something.

TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
where A is 65 and a is 97. For testing and development I've found that
putting the value in question up in the form caption and/or in the immediate
window is helpful:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Me.Caption = KeyAscii
Debug.Print KeyAscii
End Sub

HTH. Best wishes Harald

 

[Jon Peltier]

Harald old pal -

How do you read the clipboard content? I was letting the paste occur
into a textbox, then I parsed that to turn garbage into data. But
reading the clipboard content sounds much more elegant.

- Jon

 

[Dave Peterson]

Until Harald returns, you could review Chip Pearson's site:
http://www.cpearson.com/excel/clipboar.htm

Harald old pal -

How do you read the clipboard content? I was letting the paste occur
into a textbox, then I parsed that to turn garbage into data. But
reading the clipboard content sounds much more elegant.

- Jon

 

[Jon Peltier]

Thanks, Dave. I thought it was going to be worse than that, API calls
and the like. Also, thanks, Chip. I should have thought of visiting this
online encyclopedia.

- Jon