Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Class module to filter textbox entry
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Class module to filter textbox entry
To all

I have a module I am trying to use to filter the data entry of all textbox's
so as only to allow integers. I can do it by entering all the textbox
numbers by hand etc.. but was hoping to filter based on the class module.

Below is the code but am stuck as to how to pass the text box name to the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True, False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Class module to filter textbox entry
I found a solution that works, I just placed the public function inside the
class module and it worked fine!

Thanks for being a sounding board 8-)

Shawn

"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of all
textbox's
so as only to allow integers. I can do it by entering all the textbox
numbers by hand etc.. but was hoping to filter based on the class module.

Below is the code but am stuck as to how to pass the text box name to the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True, False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Class module to filter textbox entry
Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


worked for me.

--
Regards,
Tom Ogilvy



"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of all
textbox's
so as only to allow integers. I can do it by entering all the textbox
numbers by hand etc.. but was hoping to filter based on the class module.

Below is the code but am stuck as to how to pass the text box name to the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True, False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Class module to filter textbox entry
Tom

Thanks for the quick reply, I found a kludge work around but the version
you show is more elegant (If I can get it to work)

I tried it as you typed it and no go.... got a "method or data member not
found" error.

Not sure why it worked for you and not for me, need to play with it longer

Anyway any other help would be appreciated!

Shawn

"Tom Ogilvy" wrote in message
...
Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


worked for me.

--
Regards,
Tom Ogilvy



"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of all
textbox's
so as only to allow integers. I can do it by entering all the textbox
numbers by hand etc.. but was hoping to filter based on the class
module.

Below is the code but am stuck as to how to pass the text box name to
the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True, False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Class module to filter textbox entry
I also declared

Dim Coll as Collection

at the top of the userform module outside any procedure.

Other than that, I don't recall altering your code.

--
Regards,
Tom Ogilvy

"Shawn" wrote in message
...
Tom

Thanks for the quick reply, I found a kludge work around but the version
you show is more elegant (If I can get it to work)

I tried it as you typed it and no go.... got a "method or data member not
found" error.

Not sure why it worked for you and not for me, need to play with it longer

Anyway any other help would be appreciated!

Shawn

"Tom Ogilvy" wrote in message
...
Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


worked for me.

--
Regards,
Tom Ogilvy



"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of all
textbox's
so as only to allow integers. I can do it by entering all the textbox
numbers by hand etc.. but was hoping to filter based on the class
module.

Below is the code but am stuck as to how to pass the text box name to
the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True, False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE








Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Class module to filter textbox entry
Tom

I tried that as well (also in the declare field of a module so it is
available everywhere.. but)

Not sure why mine isn't working. What are you using for your excel??

Just made a simple userform with 3 textboxes.

Then made the class module called ctbx

as follows...

Public WithEvents TextCtrl As MSForms.TextBox


Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


USERFORM1 code 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

Still get error, any other idea's??

Shawn



"Tom Ogilvy" wrote in message
...
I also declared

Dim Coll as Collection

at the top of the userform module outside any procedure.

Other than that, I don't recall altering your code.

--
Regards,
Tom Ogilvy

"Shawn" wrote in message
...
Tom

Thanks for the quick reply, I found a kludge work around but the
version
you show is more elegant (If I can get it to work)

I tried it as you typed it and no go.... got a "method or data member
not
found" error.

Not sure why it worked for you and not for me, need to play with it
longer

Anyway any other help would be appreciated!

Shawn

"Tom Ogilvy" wrote in message
...
Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


worked for me.

--
Regards,
Tom Ogilvy



"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of all
textbox's
so as only to allow integers. I can do it by entering all the
textbox
numbers by hand etc.. but was hoping to filter based on the class
module.

Below is the code but am stuck as to how to pass the text box name
to
the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True,
False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE










Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Class module to filter textbox entry
Private Sub CheckKeyPress(tb As MSForms.TextBox, _

should be

Public Sub CheckKeyPress(tb As MSForms.TextBox, _


--
Regards,
Tom Ogilvy

"Shawn" wrote in message
...
Tom

I tried that as well (also in the declare field of a module so it is
available everywhere.. but)

Not sure why mine isn't working. What are you using for your excel??

Just made a simple userform with 3 textboxes.

Then made the class module called ctbx

as follows...

Public WithEvents TextCtrl As MSForms.TextBox


Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


USERFORM1 code 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

Still get error, any other idea's??

Shawn



"Tom Ogilvy" wrote in message
...
I also declared

Dim Coll as Collection

at the top of the userform module outside any procedure.

Other than that, I don't recall altering your code.

--
Regards,
Tom Ogilvy

"Shawn" wrote in message
...
Tom

Thanks for the quick reply, I found a kludge work around but the
version
you show is more elegant (If I can get it to work)

I tried it as you typed it and no go.... got a "method or data member
not
found" error.

Not sure why it worked for you and not for me, need to play with it
longer

Anyway any other help would be appreciated!

Shawn

"Tom Ogilvy" wrote in message
...
Private Sub TextCtrl_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)

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

End Sub


worked for me.

--
Regards,
Tom Ogilvy



"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of all
textbox's
so as only to allow integers. I can do it by entering all the
textbox
numbers by hand etc.. but was hoping to filter based on the class
module.

Below is the code but am stuck as to how to pass the text box name
to
the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True,
False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE












Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default Class module to filter textbox entry
Tom

Thanks, that did it!

Shawn

"Tom Ogilvy" wrote in message
...
Private Sub CheckKeyPress(tb As MSForms.TextBox, _

should be

Public Sub CheckKeyPress(tb As MSForms.TextBox, _


--
Regards,
Tom Ogilvy

"Shawn" wrote in message
...
Tom

I tried that as well (also in the declare field of a module so it is
available everywhere.. but)

Not sure why mine isn't working. What are you using for your excel??

Just made a simple userform with 3 textboxes.

Then made the class module called ctbx

as follows...

Public WithEvents TextCtrl As MSForms.TextBox


Private Sub TextCtrl_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

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

End Sub


USERFORM1 code 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

Still get error, any other idea's??

Shawn



"Tom Ogilvy" wrote in message
...
I also declared

Dim Coll as Collection

at the top of the userform module outside any procedure.

Other than that, I don't recall altering your code.

--
Regards,
Tom Ogilvy

"Shawn" wrote in message
...
Tom

Thanks for the quick reply, I found a kludge work around but the
version
you show is more elegant (If I can get it to work)

I tried it as you typed it and no go.... got a "method or data
member
not
found" error.

Not sure why it worked for you and not for me, need to play with it
longer

Anyway any other help would be appreciated!

Shawn

"Tom Ogilvy" wrote in message
...
Private Sub TextCtrl_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)

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

End Sub


worked for me.

--
Regards,
Tom Ogilvy



"Shawn" wrote in message
...
To all

I have a module I am trying to use to filter the data entry of
all
textbox's
so as only to allow integers. I can do it by entering all the
textbox
numbers by hand etc.. but was hoping to filter based on the
class
module.

Below is the code but am stuck as to how to pass the text box
name
to
the
module

AHGA!!

Shawn

' class module called CTBX

Public WithEvents TextCtrl As MSForms.TextBox

Private Sub TextCtrl_KeyPress(ByVal KeyAscii As
MSForms.ReturnInteger)

Call UserForm4.CheckKeyPress(TextCtrl.Name, KeyAscii, True,
False)

End Sub

' userform initialization on my form!

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

PUBLIC SUB TO FILTER THE INPUT

Public 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 it is not a number (0 thru 9)...

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

NOTE: CODE WAS PIECED TOGETHER FROM NUMBEROUS SOURCES ONLINE














Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
CLASS MODULE & SIMPLE MODULE FARAZ QURESHI Excel Discussion (Misc queries) 1 September 7th 07 09:32 AM
let and get in class module Doug Glancy Excel Programming 3 June 30th 04 02:30 PM
Class Module Todd Huttenstine Excel Programming 2 May 21st 04 11:17 PM
Class module David Excel Programming 4 December 31st 03 01:58 PM
Variable from a sheet module in a class module in XL XP hglamy[_2_] Excel Programming 2 October 14th 03 05:48 PM


All times are GMT +1. The time now is 11:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement