How can i control the with class module respective textboxes with keypress and change event ?

[NimishK]

Hello I would like
1. txtFrm5 to trigger the KeyPress Event with numerical values only
2. txtFrm7 to Automatically calculate discount of 10% in respective textbox Change event
i.e txtFrm7.value = txtFrm5.Value - (txtFrm5.Value*10/100)
3.TxtFrm7 should KeyAscii = 0 so no one enters the data

Here what happens all the textboxes with KeyPress event allows only numerical values
so How can i control the with class module the above mentioned respective textboxes with keypress and change event ?

https://www.dropbox.com/s/uqr00472qamkt3b/SpecificRequirementsTextBoxesClassEvent.xlsm?dl=0
In Class Module Class2AllTextboxes

Option Explicit
Public WithEvents AllTextboxesEvent As MSForms.TextBox

Private Sub AllTextboxesEvent_Change()
Dim i As Integer
Dim Ws As Worksheet

Set Ws = Worksheets("Sheet2")
Ws.Activate
If EditMode = True Then Exit Sub

For i = 1 To 7
   Ws.Cells(curRow, i).Value = UserForm2.Controls("txtFrm" & i).Value
Next i
End Sub

Private Sub AllTextboxesEvent_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case 48 To 57
        Exit Sub
        
    Case Else
        KeyAscii = 0
        MsgBox "Only numbers allowed.", 48, "Numbers only please."
        Exit Sub
    End Select

End Sub

in Module1

Option Explicit
Public row As Long
Public Ws As Worksheet
Public Const StartRow As Long = 2
Public curRow As Long, curRec As Integer
Public newUf1txtBxFrm As MSForms.TextBox
Public lablFrm2 As Control
Public EditMode As Boolean

inUserForm1

Option Explicit


Private Sub cmdUF2_Click()
  Dim Ws As Worksheet

EditMode = True
Load UserForm2
UserForm2.Show vbModeless
UserForm2.Caption = "Trial"
UserForm2.Top = 210
UserForm2.Left = 200


Set Ws = Worksheets("Sheet2")
Ws.Activate

GetRecord curRow
  EditMode = False
End Sub


Private Sub UserForm_Activate()
  UserForm1.Left = 245
End Sub

Private Sub UserForm_Initialize()
   Dim Ws As Worksheet
   Set Ws = Worksheets("Sheet2")
   Ws.Activate
curRec = 1
curRow = 2
Rows(curRow).Select

End Sub

Public Sub GetRecord(ByVal row As Long)
Dim Ws As Worksheet
Dim i As Integer
Set Ws = Worksheets("Sheet2")
    Ws.Activate
  If row < StartRow Then row = StartRow
For i = 1 To 6 '2
   UserForm2.Controls("txtFrm" & i).Value = Ws.Cells(row, i).Value
Next i
      Rows(row).Select
    curRec = curRow - 1
End Sub

In Userform2

Option Explicit
Public AllTextboxes As New Collection
Public Uf1txtBxFrm As New Class2AllTextboxes

Private Sub UserForm_Initialize()
   Call designForm2
End Sub

Public Sub designForm2()
Dim allTxtBxes As Class2AllTextboxes

Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
Ws.Activate

Dim i As Integer
Dim x As Integer
Dim y As Integer

y = 10
x = 10

Set AllTextboxes = New Collection

For i = 1 To 7
Set allTxtBxes = New Class2AllTextboxes

Set newUf1txtBxFrm = UserForm2.Controls.Add("Forms.TextBox.1")  
Set allTxtBxes.AllTextboxesEvent = newUf1txtBxFrm
AllTextboxes.Add Item:=allTxtBxes

Set lablFrm2 = UserForm2.Controls.Add("Forms.Label.1")

 With lablFrm2
        .Name = "lblfrm2" '& nNames(i)
        .Height = 30
        .Width = 15 * 5
        .Left = x
        .Top = y
        .BackStyle = 0
        .Caption = Ws.Cells(1, i).Value & vbCrLf & "txtFrm" & i
  End With

  With newUf1txtBxFrm      'txtBxFrm2
        .Name = "txtFrm" & (i) '& nNames(i)
        .Height = 18
        .Width = 116
        .Left = x
        .Top = y + 30
        .Font.Name = "Calibri"
        .Font.Size = "11"
    End With
    
    x = x + 142

If i = 2 Then
        x = 10 '15
        y = lablFrm2.Height + newUf1txtBxFrm.Height + y
End If
    
If i = 4 Then
        x = 10 '15
        y = lablFrm2.Height + newUf1txtBxFrm.Height + y
End If

If i = 6 Then
        x = 10 '15
        y = lablFrm2.Height + newUf1txtBxFrm.Height + y
End If
Next i

End Sub

Thanks NimishK

 

[Kyle123]

If you know how many textboxes you need in advance, why bother with a class module? - Just code it all at design time

 

[NimishK]

If you know how many textboxes you need in advance, why bother with a class module? - Just code it all at design time

In that case i would not have posted this thread

 

[Kyle123]

Is this a learning exercise then? Your example is quite contrived. Just create a class module for each thing you want to do.

Or is this a real problem? My answer depends on your situation

 

[Kyle123]

An example of how one may do this:
UserForm

Option Explicit

Private NumericOnlyTbs(0 To 1) As NumericOnlyTbHnd
Private NoEntryTbs(0 To 1) As ReadOnlyTbHnd
Private WithEvents watcher As EventSink



Private Sub UserForm_Click()
    
    Dim hnd As Object
    
    Set hnd = New NumericOnlyTbHnd
    Set hnd.tb = Me.tb1
    Set NumericOnlyTbs(0) = hnd
    
    Set hnd = New NumericOnlyTbHnd
    Set hnd.tb = Me.tb2
    Set NumericOnlyTbs(1) = hnd
    
    Set hnd = New ReadOnlyTbHnd
    Set hnd.tb = Me.tb3
    Set NoEntryTbs(0) = hnd

    Set hnd = New ReadOnlyTbHnd
    Set hnd.tb = Me.tb4
    Set NoEntryTbs(1) = hnd
    
    Set watcher = New EventSink
    Set watcher.tb = Me.tb1
    
End Sub

Private Sub watcher_changed(value As Variant)
    Me.tb5 = value * 0.9
End Sub

Class ReadOnlyTbHnd

Public WithEvents tb As MSForms.TextBox

Private Sub tb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = 0
    MsgBox "No entry", vbCritical
End Sub

Class NumericOnlyTbHnd

Public WithEvents tb As MSForms.TextBox

Private Sub tb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case 48 To 57
            Exit Sub
            
        Case Else
            KeyAscii = 0
            MsgBox "Only numbers allowed.", 48, "Numbers only please."
            Exit Sub
        End Select
End Sub

Class EventSink

Public Event changed(ByRef value As Variant)
Public WithEvents tb As MSForms.TextBox
Private Sub tb_Change()
    RaiseEvent changed(tb.value)
End Sub

 

[NimishK]

Kyle
Really beautiful way of presenting the coding with colours. How you did this. ?
Yes what i posted is a real problem. Actually there are more than 67 textboxes out of which some textboxes are related to the change, key_Press and in KeyPress KeyAscii =0
But sure will give try to what you suggested for creating different Class Modules. You mean to say i need to create 3 class modules
Really Something new to learn.
You mentioned Userform in your eg so which User form should i try as per my example. Let me give a try to your suggestion.

 

[Kyle123]

I've written a Chrome Extension that formats my code tags like that: https://chrome.google.com/webstore/detail/syntax-highlighter-for-ex/lfepfbglmpkdfbjmbonacjapndgmjdoe i write the code, select it and hit Alt+a and it uses BBCode to colour it.

There are many ways to do this, based on what you've described, yes, you could create 3 class modules as per my example - though you haven't really given much to go on.

 

[NimishK]

Tried. seems difficult as per my structure.
Not able to assign

Set hnd.tb = UserForm2.Controls("txtFrm5") against yours Set hnd.tb = Me.tb1. Blunder by me

Class Modules for me still at a primitive stage

 

[NimishK]

Just Copied your code and Tried as is except i shifted from userform_click to userform_Init and physically placed 5 textboxes on userform
Excellent code for comparitvely less textboxes required on userform and changed me.tb1 to userform1.textbox1 FYI

Also observed i have to type or press any key in textbox4 to trigger which does not automatically change when typed the values in Textbox1

Private Sub watcher_changed(value As Variant)
    'Me.tb5 = value * 0.9
        Me.TextBox4.value = Me.TextBox1.value * 10
End Sub

What is desired that textboxes are created when form is loadaed. Out of that few particular textboxes are required with numeric values
For eg
in the loop

If i = 17 then ----" Lets say textbox17
    then Numeric Value only
end if
If i = 18 then ----" textbox18
    then Numeric Value only
end if
if i = 19 then ----" textbox19
KeyAscii =0
textbox19.value = Textbox17.Value  * Textbox18.Value --->' Here Value of textbox19 is automatically displayed when entring data in                                                                                                 textbox 17 and 18 respectively

Hope this clarifies

 

[Kyle123]

So which bit are you stuck on? You know the principle and know how to add Textboxes, just combine the two