Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
let and get in class module | Excel Programming | |||
Class Module | Excel Programming | |||
Class module | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |