Hi - I've tried several different things that would
be logical in a spreadhsheet to ensure that the
entry into a Textbox is a number such as:

If WorksheetFunction.IsError(Clng(Textbox1.Value)) Then

But it dies on the Type mismatch.
Is there some way to trap the entry that I'm not thinking of?

Marston

If IsNumeric(Textbox1.Text) Then


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

" wrote in message
oups.com...
Hi - I've tried several different things that would
be logical in a spreadhsheet to ensure that the
entry into a Textbox is a number such as:

If WorksheetFunction.IsError(Clng(Textbox1.Value)) Then

But it dies on the Type mismatch.
Is there some way to trap the entry that I'm not thinking of?

This was posted recently by Harald Staff: it is a method to restrict
several textboxes to only accept numbers. If you only need it for one
textbox, you can use the basic code (skip the class module) in the Userform
module or the worksheet module depending on where the textbox is located.


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

--
Regards,
Tom Ogilvy


" wrote in message
oups.com...
Hi - I've tried several different things that would
be logical in a spreadhsheet to ensure that the
entry into a Textbox is a number such as:

If WorksheetFunction.IsError(Clng(Textbox1.Value)) Then

But it dies on the Type mismatch.
Is there some way to trap the entry that I'm not thinking of?

Awesome....works like a charm.

Thank you