Excel VBA Userform – Execute Sub when something changes

by

This can be achieved by using a class module. In the example that follows I will assume that you already have a userform with some textboxes on it.

Firstly, create a class module in your VBA project (let call it clsTextBox — be sure to change the ‘Name’ property of the class module!)

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property

Private Sub MyTextBox_Change()
    AutoCalc() //call your AutoCalc sub / function whenever textbox changes
End Sub

Now, in the userform, add the folowing code:

Dim tbCollection As Collection

Private Sub UserForm_Initialize()
    Dim ctrl As MSForms.Control
    Dim obj As clsTextBox

    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.TextBox Then
                Set obj = New clsTextBox
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing

End Sub

More Related Contents:

  1. Unprotect VBProject from VB code
  2. VBA – destroy a modeless UserForm instance properly
  3. Assign event handlers to controls on user form created dynamically in VBA
  4. VBA Excel Populate ListBox with multiple columns
  5. VBA: How to delete filtered rows in Excel?
  6. Round function in Excel, worksheet function vs VBA
  7. simple vba code gives me run time error 91 object variable or with block not set
  8. How to filter listbox values based on a Textbox value
  9. VBA Function argument list select
  10. Excel VBA wildcard search
  11. Excel Useform: How to hide application but have icon in the taskbar
  12. Reading Web Pages using Excel VBA
  13. How to get selected value in multicolumn listbox
  14. How do I get values from a hidden worksheet using VBA [closed]
  15. How to copy cells that contain certain text to another worksheet [closed]
  16. VBA Macro for copying and pasting columns on same excel sheet
  17. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  18. Levenshtein Distance in VBA [closed]
  19. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  20. How do I base64 encode a string efficiently using Excel VBA?
  21. Conditional Formatting using Excel VBA code
  22. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  23. What does ReDim Preserve do?
  24. How to create a separate CSV file from VBA?
  25. Cannot VBA write data to cells in Excel 2007/2010 within a function
  26. Convert RTF (Rich Text Format) code into plain text in Excel
  27. lastrow and excel table.
  28. Open a workbook from VBA and disable Workbook_Open() code?
  29. How do I activate a specific workbook and a specific sheet?
  30. Delete all data rows from an Excel table (apart from the first)

Leave a Comment