+ Reply to Thread
Results 1 to 7 of 7

UserForm Change Event

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    UserForm Change Event

    Hello

    Is it possible to activate some code whenever anything or a selected bunch of textboxes change?

    So instead of using this code 15 times:

    Private Sub txtPTno1_Change()
    Sheets("Personal Tax").Range("C2").Value = txtPTno1.Value
    End Sub
    I could use something like:

    Private Sub frmQuote_Change()
    Dim x As Long
    For x = 1 To 15
        Sheets("Personal Tax").Range("C" & x).Offset(x - 2).Value = Me.Controls("txtPTno" & x).Value
    Next x
    End Sub
    I only ask as my UserForm may end up having lots of these little bits of code, with the only differences being the numbers.

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,298

    Re: UserForm Change Event

    For a Change event, you could use a class module to trap the event for all the relevant textboxes. If you assign the address of the cell to change to the Tag property of each textbox, the code would be fairly straight forward.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: UserForm Change Event

    Thanks for the response Rory.

    I'm afraid this is nothing I've ever come across before so wouldn't know where to start!

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: UserForm Change Event

    Open the VBEditor and insert a class module. Use the Properties window to change its name to clsAnyTextBox
    Then put this code in that class module.
    ' in clsAnyTextBox module
    
    Public WithEvents TextBox As MSForms.TextBox
    
    Event Change()
    
    Private Sub TextBox_Change()
        Set TextBox.Parent.anyTextBox = Me
        RaiseEvent Change
    End Sub

    Then you can put code like this in your userform's code module
    ' in userform code module
    
    Public WithEvents anyTextBox As clsAnyTextBox
    Dim myTextBoxes As Collection
    
    Private Sub UserForm_Initialize()
        Dim oneTextbox As Variant
        Dim oneAny As clsAnyTextBox
        Dim I As Long
        Set myTextBoxes = New Collection
        
        Rem matches each textbox of interest to its linked cell
       For I = 0 to 2
             Array(Me.TextBox1, Me.TextBox2, Me.TextBox3)(I) = Array(Range("A1"),Range("B2"),Range("C3"))(I).Address(,,,true)
        Next i
    
        
        Rem for each textbox of interest
        '   creates a clsAnyTextBox object,
        '   assigns that text box to that custom object,
        '   and puts the custom object in a collection
      For Each oneTextbox In Array(Me.TextBox1, Me.TextBox2, Me.TextBox3)
            Set oneAny = New clsAnyTextBox
            Set oneAny.TextBox = oneTextbox
            myTextBoxes.Add Item:=oneAny, Key:=oneTextbox.Name
        Next oneTextbox
        
        Set oneAny = Nothing
    End Sub
    
    Private Sub anyTextBox_Change()
        With anyTextBox.TextBox
            msgBox .Name & " has changed."
            Range(.Tag) = .Text
        End With
    End Sub
    Your loop through textboxes (in the Initialize event) might be different.

    Note that the userform variable anyTextBox is a clsAnyTextBox object. To refer to the textbox itself, use anyTextBox.TextBox
    Last edited by mikerickson; 11-10-2014 at 12:00 PM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,298

    Re: UserForm Change Event

    Untested but something like this:
    1. Add a new Class module to your project, change its name to CTextboxHandler, then paste this code in:
    Option Explicit
    
    Private WithEvents m_tb As MSForms.Textbox
    Private m_rngLink As Excel.Range
    Public Property Set Textbox(tb As MSForms.Textbox)
        Set m_tb = tb
    End Property
    Public Property Get Textbox() As MSForms.Textbox
        Set Textbox = m_tb
    End Property
    Public Property Set CellLink(rng As Excel.Range)
        Set m_rngLink = rng
    End Property
    Private Sub m_tb_Change()
        m_rngLink.Value = m_tb.Value
    End Sub
    Now add this to your Userform code:
    at the top of the code module before any routines:
    Dim colTBs as Collection
    Now in the Userform_Initialize code you need:

    Private Sub Userform_Initialize()
    Dim oHandler as CTextboxHandler
    Dim x As Long
    
    set colTBs = New Collection
    
    For x = 1 To 15
       Set oHandler = New CTextboxHandler 
       set oHandler.Textbox = Me.Controls("txtPTno" & x)
       Set oHandler.CellLink = Sheets("Personal Tax").Range("C" & x).Offset(x - 2)
       colTBs.add oHandler
    Next x
    End Sub

  6. #6
    Forum Contributor
    Join Date
    04-04-2014
    Location
    Tetbury, England
    MS-Off Ver
    Excel 2010
    Posts
    254

    Re: UserForm Change Event

    Rory, when I try to run the form, an error pops up saying:

    Application-defined or object defined error

    With no debug

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,298

    Re: UserForm Change Event

    Can you post it?

    Never mind - the problem is here:
    Set oHandler.CellLink = Sheets("Personal Tax").Range("C" & x).Offset(x - 2)
    When x is 1 you're trying to refer to cell C0 which doesn't exist. You need to adjust the logic to whatever fits your layout.
    Last edited by romperstomper; 11-10-2014 at 12:28 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change userform listbox border with change event
    By EuclideanKraken in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2014, 05:34 PM
  2. Can a UserForm have a change event?
    By PDBartlett in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2014, 11:44 AM
  3. cant keep typing in userform textbox after change event
    By jed38 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-01-2014, 09:13 AM
  4. Couple of Userform, Change event queries
    By welshman010 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-24-2014, 08:46 PM
  5. Userform Multipage Event Change
    By sepandb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2008, 01:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1