Solved: Another userform query

[blackie42]

The following code is in a userform and what it does is automatically blanks the entry if more than 4 decimal places are input. So the user has to reinput in the textbox. What I'd like to do instead is force the user to input a 0(zero) followed by 4DP ie. if the first character typed isn't a 0 then do not allow any further typing and then it has to be 4 numbers after the . -> is this possible?

[vba]Private Sub Textbox1_Change()
If (Not IsNumeric(TextBox1.Value) And (TextBox1.Value <> "")) Then
TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
End If
If (InStr(TextBox1, ".") < (Len(TextBox1) - 4)) And (InStr(TextBox1, ".") > _
0) Then
TextBox1 = ""
End If
End Sub[/vba]
2nd issue is that I have 42 such textboxes that interact with a single sheet - is there any way to apply such code to each textbox without having to have separate change code for every textbox

Any help much appreciated

Jon

[rory]

You could set the MaxLength property of the textbox to 6, then use something like this (note: only tested it a bit - I prefer to validate afterwards):
[VBA]Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 49 To 57 ' numbers 1-9
If Left$(TextBox1, 2) <> "0." Then TextBox1 = "0."
Case 46 ' decimal point
If Len(TextBox1) = 0 Then
TextBox1 = "0"
ElseIf InStr(TextBox1, ".") > 0 Then
KeyAscii = 0
End If
Case 48 ' zero
If Len(TextBox1) > 0 And Left$(TextBox1, 2) <> "0." Then TextBox1 = "0."
Case Else
KeyAscii = 0
End Select

End Sub
[/VBA]

For multiple textboxes, you can use a class with a textbox variable declared public withevents and then add new instances of the class to a collection, assigning each textbox you want monitored to the textbox object of the class.

[blackie42]

Thanks Rory - if you press a 1 to start with it defaults to 0.1 - which is OK - all prices entered are below 1 - so the user would have to backspace and correct.

Is there any chance of a starter on the other bit as I ain't no expert and not familiar with 'class' modules.

thanks for your help

Jon

[rory]

See if the attached helps you.

[mikerickson]

I changed this after first posting. It seems harsh to send a user back to square 1 for just entering a 5'th digit. This routine doesn't blank the textbox. It just ignores the 5th + digits entered.
[VBA]Private Sub Textbox1_Change()
If (Not IsNumeric(TextBox1.Value) And (TextBox1.Value <> "")) Then
TextBox1.Value = Left(TextBox1.Value, Len(TextBox1.Value) - 1)
End If
If (InStr(TextBox1, ".") < (Len(TextBox1) - 4)) And (InStr(TextBox1, ".") > _
0) Then
TextBox1.Text = Format(Val(TextBox1.Text), "#.0000")
End If
End Sub[/VBA]

[blackie42]

Thanks Rory - I have managed to import your code in to the project and it works fine on the first few text boxes. I'll fully test tomorrow. I'll try and work out what its doing and how when I get chance.

And thanks Mike - yes a small change in the code for the other form would be a good idea.

regards

Jon

[rory]

The only thing to be aware of is that, as written, the code will apply that validation to every textbox on the form. If there are any you don't want validated that way, you will need to add some form of additional check - such as a naming convention or tag on the textbox.