I need to add validation to all my textboxes in my userform. Th
userform has textboxes connected to a worksheet (using controlsource
on which I create some graphs. I'll be looking at validating correc
ranges, positive numbers, string lengths and so on.

I've been looking at a couple of solutions. Range.validation.add an
the BeforeUpdate event, but none seem to work as I want.

I've also found something called Micorsoft Masked Edit Control, but i
seems my licence doesn't include it.

How can I catch invalid input before it generates errors in my code? A
it it now, users can input values that create division by zero and s
on.

Thanks in advance!

On a side note: If a number is entered that creates a division by 0
the formula in my cell gets deleted. Why is that

--
Message posted from http://www.ExcelForum.com

Hi

I always do this in Keydown and Keypress, like this (for decimal number
entries):

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If Shift = 2 Then 'Ctrl
If KeyCode = 86 Then 'V
'paste. Decide what to do, or/and
'discard like this:
KeyCode = 0
End If
End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
Select Case KeyAscii
Case 45
'minus, must be first
If .SelStart 0 Then KeyAscii = 0
Case 46 'decimal separator, one only
If InStr(.Text, ".") 0 Then KeyAscii = 0
Case 48 To 57
'numbers, ok
Case Else
KeyAscii = 0
End Select
End With
End Sub

It takes some coding, but has proven itself very robust and reliable.

HTH. Best wishes Harald

"phreud " skrev i melding
...
I need to add validation to all my textboxes in my userform. The
userform has textboxes connected to a worksheet (using controlsource)
on which I create some graphs. I'll be looking at validating correct
ranges, positive numbers, string lengths and so on.

I've been looking at a couple of solutions. Range.validation.add and
the BeforeUpdate event, but none seem to work as I want.

I've also found something called Micorsoft Masked Edit Control, but it
seems my licence doesn't include it.

How can I catch invalid input before it generates errors in my code? As
it it now, users can input values that create division by zero and so
on.

Thanks in advance!

On a side note: If a number is entered that creates a division by 0,
the formula in my cell gets deleted. Why is that?


---
Message posted from http://www.ExcelForum.com/

Thanks for your reply. I tried it and it worked great. I can see tha
it's very robust.

However, I have up towards 50 textboxes, and it will cause too muc
coding. I solved the problem myself though. I'm using the afterupdat
event to check the value entered, and it automatically reverts back t
the previous value if it's not valid.

The reason I got confused was, while trying it out I had a textbo
displaying the result of an equation in the worksheet. The textbox wa
linked to the cell with the equation using Controlsource, and tha
caused it to overwrite my equation all the time. I guess that's a bi
"no no" :)

/ phreu

--
Message posted from http://www.ExcelForum.com

"phreud " skrev i melding
...
Thanks for your reply. I tried it and it worked great. I can see that
it's very robust.

However, I have up towards 50 textboxes, and it will cause too much
coding.

If many textboxes are to behave identically, that's what class modules are
for. One set of code used by tons of instances. Post back if you want a
class solution, it's far easier than one might think.

You have a working solution already, so this only if you're interested in
how it's done.

Best wishes Harald

I've run into problems. I need to prevent users from entering period
(.) as comma separator, so your solution might come in handy afte
all.

I'm totally lost when i comes to class modules, so...shoot! :

--
Message posted from http://www.ExcelForum.com

"phreud " skrev i melding
...
I'm totally lost when i comes to class modules, so...shoot! :)

Sure. All of this in the VB editor:

1 Menu Insert Class module. A blank class module appear. Name it
NumTxt -naming is important.

2 type (or paste) this into it:

Option Explicit

Public WithEvents Box As MSForms.TextBox

3 Now you have a Box object in there, and can appproach its events in the
dropdowns above the module. make this code (or paste it in):

Private Sub Box_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift = 2 Then 'Ctrl
If KeyCode = 86 Then 'V
'paste. Decide what to do, or/and
'discard like this:
KeyCode = 0
End If
End If
End Sub

Private Sub Box_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
With Box
Select Case KeyAscii
Case 45
'minus, must be first
If .SelStart 0 Then KeyAscii = 0
Case 48 To 57
'numbers, ok
Case Else
KeyAscii = 0
End Select
End With
End Sub

4 You don't say where your boxes are. If it's on a userform, put this into
the userform module. If it's on a worksheet, put it into the worksheet
module:

Option Explicit

Dim Box1 As New NumTxt
Dim Box2 As New NumTxt
Dim Box3 As New NumTxt
Dim Box4 As New NumTxt
Dim Box5 As New NumTxt
' and so on as many as you need...

Public Sub AssignClasses()
Set Box1.Box = Me.TextBox1
Set Box2.Box = Me.TextBox2
Set Box3.Box = Me.TextBox3
Set Box4.Box = Me.TextBox4
Set Box5.Box = Me.TextBox5
' and so on...
'and that's all
End Sub

4 Now all you have to do is run the AssignClasses macro before use, either
like

Sub Makro1()
Call UserForm1.AssignClasses
UserForm1.Show
End Sub

or on workbook opening for sheet textboxes:

Private Sub Workbook_Open()
Call Sheet1.AssignClasses
End Sub

and kaboom, all your assigned boxes refuse to accept anything but a leading
minus and number entries. Now how cool is that ?

(Note that Case 46 is removed, it's the decimal separator in my first
suggestion. Remove also Case 45 if minus is forbidden.)

HTH. Best wishes Harald

Thanks alot Harald

--
Message posted from http://www.ExcelForum.com