Userform dynamically created Textbox – Run code after Exit (or tab) of textbox

[netuser]

Hi,

I was looking for a way to do Calculation inside a Textbox (created dynamically) and found a great peace of code that let me do it (see below).

I can make it work perfectly if I add a command button to calculate but I want it to fire after user exit that particular Textbox (I have 20 others too).

My search gave me a code that let me do it but only on Textbox change, but that just do that calculation at the middle of typing and don’t let me finish typing the formula.

I searched all around and found a lot of complicated code here posted by @Jaafar Tribak and @Rory but I am unable to adopt the code to work with mine.

Here is the code I am using currently if anyone could help me adopt for EXIT than Change , it will greatly appreciated

Thanks in advance

In my Userform :

Private Sub CheckBox1_Change()
 
Dim  Rng As Range
Dim myObj As clsEventTrapper
Dim ctl As MSForms.Control
Set myCollection = New Collection
 
Set Rng = Worksheets("Temp").Range("A2:A10")
 
For Each Cell In Rng
 
NumC = Cell
 
Set ctl = UserForm1.MultiPage1.Pages.Item(counter).Controls.Add("Forms.Label.1", "PALabel" & Cell, Visible)
With ctl
        .Caption = "Gross Amount:"
        .Height = 24
        .Top = 68
        .Width = 78
        .Left = 180
    End With
 
Set ctl = UserForm1.MultiPage1.Pages.Item(counter).Controls.Add("Forms.Textbox.1", "PACie" & Cell, Visible)
      With ctl
        .Height = 24
        .Top = 68
        .Width = 78
        .Left = 270
        .Text = 0
End With
 
Set myObj = New clsEventTrapper 'Create new instance of object
myObj.Initialize ctl, "Callback_2" 'Pass newly created control and the name of a callback function into object
myCollection.Add myObj 'Add to collection so object persists
 
Next
 
End sub

In Class Module

Option Explicit
 
Private WithEvents ctlCommandButton As MSForms.CommandButton
Private WithEvents ctlTextBox As MSForms.TextBox
Private WithEvents ctlCheckbox As MSForms.CheckBox
'...Add more control types, declaring WithEvents, as required.
 
Private strEventProc As String
 
 
'--------------------------------------------------------------------------------------
'In your form, as you dynamically create controls, create an instance of this class and
'call this procedure, passing in the created control and the name of a callback
'function to execute in response to its events
'--------------------------------------------------------------------------------------
Public Sub Initialize(ByRef ctl As MSForms.Control, ByVal strProc As String)
 
    If TypeOf ctl Is MSForms.CommandButton Then
        Set ctlCommandButton = ctl
    ElseIf TypeOf ctl Is MSForms.TextBox Then
        Set ctlTextBox = ctl
       
    ElseIf TypeOf ctl Is MSForms.CheckBox Then
    Set ctlCheckbox = ctl
   
    End If
    '...Add more control types as required
      
      
    strEventProc = strProc
 
End Sub
 
Private Sub Class_Terminate()
    Set ctlCommandButton = Nothing
    Set ctlTextBox = Nothing
    Set ctlCheckbox = Nothing
End Sub
 
'-------------------------------------------------------------------
'Event handlers for private control members.
'When fired, they execute the callback function specified by the user.
'-------------------------------------------------------------------
Private Sub ctlCommandButton_Click()
    Application.Run strEventProc
End Sub
 
Private Sub ctlTextBox_Change()
    Application.Run strEventProc
End Sub
 
 
Private Sub ctlCheckbox_Change()
    Application.Run strEventProc
   
End Sub

In Module :

Public NumC
 
Public Sub Callback_2()
 
 
Dim ws As Worksheet
    Dim rLast As Range
    
     'copy the text from the text box to a cell as if it was a formula
     'there are many ways to do this, one way is to create a temporary worksheet
     'a simpler way is to use a cell outside the usedrange to hold the formula
    
     'you could in theory calculate a global value for rLast in eg, the Userform Initialize code
     'if the UsedRange won't change during the life of this UserForm
    
     'assume the workbook contains 1 sheet and use the first sheet
    Set ws = ThisWorkbook.Worksheets("Test")
     'get an unused cell outside the used range
    Set rLast = ws.UsedRange.Cells(ws.UsedRange.Rows.Count + 1, ws.UsedRange.Columns.Count + 1)
    
     'put in some error handling so that if an invalid
     'formula is in the textbox, it will not accept the value
    With rLast
        On Error Resume Next
         'the unused cell will be used to interpret the calculation formula in the textbox
        .Formula = "=IFERROR(" & UserForm1.MultiPage1.Pages(0).Controls("PACie" & NumC).Value & ","""")"
        On Error GoTo 0
        If .Value <> "" Then
        Debug.Print .Value
            UserForm1.MultiPage1.Pages(0).Controls("PACie" & NumC).Value = .Value
        Else
             'throw in a beep if required
            Beep
             'alternatively uncomment the next line to clear the textbox in case of error
             'TextBox1.Value = ""
            
             'and stop the user from moving off the field
            Cancel = True
        End If
         'remove the formula from the temporary cell used
        .ClearContents
    End With
 
End Sub

 

[netuser]

Thanks Norie for the reply, perseverance paid lol

I need to have all those individual Text boxes as the user need to enter data in each of them.

To simplify here is what I require to do with this forum, I ll take example of 2 text boxes so that it is easy to understand.

Gross Amount
Taxes


People have multiple gross and taxes amounts that they need to do addition in calculator to get the total. I wanted to avoid this Calculation and let them do the calculation directly inside the Textbox. I found the great code that let me do this (posted in my OP 3rd code). But right now I have put a command button that let me do this. What I would love to have is that when Someone get out of the Textbox (exit) it do that calculation (with Tab or Click in another text box etc..).

As you have seen I am only able to achieve this with Change and not Exit. With change it will not work as when user want to enter 20+33 the code fires after first 3 so it don't let them enter 33 and do the addition of 20+3 instead and show 23.

I hope I am clear in explanation and you see what my issue is. I am forced to create Dynamic Textboxes because these text boxes are inside Multipage and I don't know in advance how many pages there will be as they are based on other conditions (but that is not the issue).

 

[netuser]

Norie any help please ?

thanks