0

Let's say I have a user form with 5 rows. Each row consists of multple controls, for an example:

<Text field> | <Text field> | <Combo box>

When the value of the first control/column (text field) is changed in either of the rows, I have a function I would like to call. Is this possible without making five seperate _Change functions (one for each row)?

Community
  • 1
  • 1
Jakob Busk Sørensen
  • 5,599
  • 7
  • 44
  • 96
  • Will the function that is called upon affect a specific control (like the one you changed, or the ones in the same row), or is it something global (like printing the time into one particular textbox)? – Martin Dreher Jan 03 '17 at 09:40
  • It will affect specific controls, but not the ones that calls the functions. For example it could affect a summary text field, showing the sum of alle the values in the first column (if all the inputs where numeric). – Jakob Busk Sørensen Jan 03 '17 at 10:07
  • kk. In the specific case of a `SUM`-Field, you could think about adding a `.ControlSource` with `[Text0]+[Text1]+....` to **that one specific** sum field (manually or with a loop over your controls). No need to mess with the events of your inputfields then. For a more complex function that requires VBA, see my post below. – Martin Dreher Jan 03 '17 at 10:11
  • But is there anyway to trigger an update of that specific field, when one of the controls (text fields) changes? Maybe like a way to check for any changes in the entire form? – Jakob Busk Sørensen Jan 03 '17 at 10:23

1 Answers1

0

There is a reasonably quick, but unreasonably dirty way of adding Eventhandlers to several controls...

i) define the function you want to call with Change. Afaik it has to be a Public Function in a normal module

ii) create an Access-makro (not VBA) with a RunCode-Event that calls your function.

enter image description here

iii) add the event to your controls, either in your design view or with something like

Private Sub Form_Current()
    Dim ctl
    For Each ctl In Me.Controls
        With ctl
            If .ControlType = acTextBox Then
                .OnChange = "Makro1"
            End If
        End With
    Next ctl        
End Sub

Note:

  • the above code adds the event to all textboxes (due to me being lazy). swap the If .ControlType = acTextBox Then out for a check of your liking. You could use tags, smart-tags, simple name conventions like tbx_c1r1 or control arrays, if you're fancy

  • this gets considerably harder if your function includes the Caller in some way. Make sure to post your function, maybe we can figure out a way something.

Hope this helps! I'll be glad if someone comes up with a smarter approach.

Martin Dreher
  • 1,514
  • 2
  • 12
  • 22
  • Thanks for the suggestion. Although dirty (as you said) it would actually fix my problem. I would prefer not having to use Access though (but maybe I have to). – Jakob Busk Sørensen Jan 03 '17 at 10:10
  • i'm terribly sorry, i somehow missed that you got excel, and not access! – Martin Dreher Jan 03 '17 at 10:44
  • No worries. If you are interested, I found a possible solution here: http://stackoverflow.com/questions/5940596/excel-vba-userform-execute-sub-when-something-changes - though I am strugling to make it work with `_Exit()` rather than just `_Change()` – Jakob Busk Sørensen Jan 03 '17 at 12:04