Class module in VBA
 

Trying to make a group of textboxes allow only integers to be entered,
not sure why this won't work. Can someone have a look and let me know
if it is possible?

First make userform with 3 textboxes on it

the code on teh form is as follows:

Dim coll As Collection
Private Sub UserForm_Initialize()
Dim Ctrl As MSForms.Control
Set coll = New Collection
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
Set TBX = New ctbx
Set TBX.TextCtrl = Ctrl
coll.Add TBX
End If
Next Ctrl
End Sub
Private Sub CheckKeyPress(tb As MSForms.TextBox, _
ByVal KeyAscii As MSForms.ReturnInteger, _
Optional ignoreDecimal As Boolean, _
Optional allowNegative As Boolean)

Dim key As String
Dim pos As Integer
Dim selLength As Integer
If KeyAscii < 127 Then
key = Chr(KeyAscii)
If Not IsNumeric(key) Then
pos = tb.SelStart
selLength = tb.selLength
If (key = decimalSep And Not ignoreDecimal) _
Or (key = "-" And allowNegative _
And pos = 0) Then
If Not ((InStr(tb, key) = 0) Or (selLength 0 _
And InStr(tb.SelText, key) 0)) Then
KeyAscii = 0
End If
Else
KeyAscii = 0
End If
End If
End If
End Sub


The class module should be called CTBX

and have the following:

Public WithEvents TextCtrl As MSForms.TextBox


Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm1.CheckKeyPress(TextCtrl, KeyAscii, True, False)

End Sub


I get an error when running that method not found. Any idea's how to
fix??

AHGA

Shawn


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

Class module in VBA
 

Change
Private Sub CheckKeyPress

to

Public Sub CheckKeyPress

and it ran fine for me.

--
Regards,
Tom Ogilvy


"romkeys " wrote in message
...
Trying to make a group of textboxes allow only integers to be entered,
not sure why this won't work. Can someone have a look and let me know
if it is possible?

First make userform with 3 textboxes on it

the code on teh form is as follows:

Dim coll As Collection
Private Sub UserForm_Initialize()
Dim Ctrl As MSForms.Control
Set coll = New Collection
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
Set TBX = New ctbx
Set TBX.TextCtrl = Ctrl
coll.Add TBX
End If
Next Ctrl
End Sub
Private Sub CheckKeyPress(tb As MSForms.TextBox, _
ByVal KeyAscii As MSForms.ReturnInteger, _
Optional ignoreDecimal As Boolean, _
Optional allowNegative As Boolean)

Dim key As String
Dim pos As Integer
Dim selLength As Integer
If KeyAscii < 127 Then
key = Chr(KeyAscii)
If Not IsNumeric(key) Then
pos = tb.SelStart
selLength = tb.selLength
If (key = decimalSep And Not ignoreDecimal) _
Or (key = "-" And allowNegative _
And pos = 0) Then
If Not ((InStr(tb, key) = 0) Or (selLength 0 _
And InStr(tb.SelText, key) 0)) Then
KeyAscii = 0
End If
Else
KeyAscii = 0
End If
End If
End If
End Sub


The class module should be called CTBX

and have the following:

Public WithEvents TextCtrl As MSForms.TextBox


Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm1.CheckKeyPress(TextCtrl, KeyAscii, True, False)

End Sub


I get an error when running that method not found. Any idea's how to
fix??

AHGA

Shawn


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