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

 

[Roderick_E]

Try AfterUpdate instead of Change

 

[netuser]

Try AfterUpdate instead of Change

You cannot use Afterupdate or Exit in Class module

 

[netuser]

Bump

 

[netuser]

Bump

 

[netuser]

Bump, i really need help

 

[netuser]

Bump of the day

 

[netuser]

Bump

 

[netuser]

Bump

 

[Norie]

I'm sorry but what you want to do isn't possible unless you use the type of code that Jaafar and Rory have posted/suggested/linked to.

What exactly are you trying to do?

Perhaps if you explained that then we could suggest some other approach.

PS You mention 20 textboxes? To me that suggests you might want to look at using some other control, perhaps a listbox?