Update cell with a value when form field is selected

Question

I have an excel workbook with modeless form. The way it's setup is that: each sheet in the workbook has a tab in the form. Each field in these tabs is Linked to a cell in corresponding sheet. So when a value is changed/updated in the form, it is automatically updated in the relevant cell. The way I am doing this is by using the onChange event for each filed which call's a UDF that does the updating. My question, there are a lot of fields in the form and lots more to be added. Is there a way to update relevant cell when a field in the form is selected without having to add the call to a UDF in onChange event for each field?

I have tried using things like ControlSource but that only one way where it just updates the value in the form but doesn't update the value in the cell when form is updated.

As a side note, unfortunately I cannot share the form or the sheet but am willing to answer any questions

EDIT

Below is the function that updates the field:

Sub UpdateWorksheetValue(ByVal oObj As Object)
    Dim oWS As Worksheet
    Dim sCurrentValue As String
    Dim iC As Long

    ' Lets check if tag is set
    If Len(Trim(oObj.Tag)) = 0 Then
        MsgBox "Empty tag found for '" & oObj.Name & "' field. Failed to update field value" & vbCrLf & vbCrLf & "Please contact system administrator with this information", vbCritical + vbOKOnly, "Update Failed"
        Exit Sub
    ElseIf Len(Trim(Mid(oObj.Tag, InStr(1, oObj.Tag, "¬") + 1))) = 0 Then
        MsgBox "Tag for '" & oObj.Name & "' field does not include page title. Failed to update field value" & vbCrLf & vbCrLf & "Please contact system administrator with this information", vbCritical + vbOKOnly, "Update Failed"
        Exit Sub
    End If

    ' Set worksheet
    Select Case LCase(Trim(Mid(oObj.Tag, InStr(1, oObj.Tag, "¬") + 1)))
        Case "client identification"
            Set oWS = oWB.Worksheets("Client Identification - Output")
        Case "request details"
            Set oWS = oWB.Worksheets("Request Details - Output")
        Case "db responsible individuals"
            Set oWS = oWB.Worksheets("DB Responsible Ind  - Output")
        Case "additional details"
            Set oWS = oWB.Worksheets("Additional Details - Output")

    End Select

    ' Set value
    With oWS

        ' Lets check if tag is set
        If Len(Trim(Mid(oObj.Tag, 1, InStr(1, oObj.Tag, "¬") - 1))) = 0 Then
            MsgBox "Tag for '" & oObj.Name & "' field does not include corresponding cell information. Failed to update field value in '" & oWS.Name & "' worksheet" & vbCrLf & vbCrLf & "Please contact system administrator with this information", vbCritical + vbOKOnly, "Update Failed"
            Exit Sub
        End If

        ' Set the search value
        .Range("Z1").Value = Mid(oObj.Tag, 1, InStr(1, oObj.Tag, "¬") - 1)
        DoEvents

        ' If a row with tag text is not found, throw a message and exit sub
        If Len(Trim(.Range("Z2").Value)) = 0 Then
            MsgBox "Unable to find corresponding cell for '" & oObj.Name & "' field in '" & .Name & "' worksheet. Failed to update field value" & vbCrLf & vbCrLf & "Please ensure that the field's 'Tag' matches a cell in the sheet or contact system administrator", vbCritical + vbOKOnly, "Update Failed"
            Exit Sub
        End If

        ' Set field value
        Select Case LCase(TypeName(oObj))
            Case "textbox", "combobox"
                .Range("B" & .Range("Z2").Value).Value = oObj.Value
            Case "optionbutton"
                If oObj.Value = True Then
                    .Range("B" & .Range("Z2").Value).Value = oObj.Caption
                Else
                    .Range("B" & .Range("Z2").Value).Value = ""
                End If
            Case "listbox"

                ' First lets the current cell value
                sCurrentValue = .Range("B" & .Range("Z2").Value).Value

                ' Now lets build the string for the cell
                For iC = 0 To oObj.ListCount - 1
                    If oObj.Selected(iC) And InStr(1, sCurrentValue, oObj.List(iC)) = 0 Then
                        sCurrentValue = sCurrentValue & "/" & oObj.List(iC)
                    ElseIf Not oObj.Selected(iC) And InStr(1, sCurrentValue, oObj.List(iC)) > 0 Then
                        sCurrentValue = Replace(sCurrentValue, "/" & oObj.List(iC), "")
                    End If
                Next

                ' And finally, set the value
                .Range("B" & .Range("Z2").Value).Value = sCurrentValue

        End Select

    End With

    ' Clear object
    Set oWS = Nothing

End Sub

EDIT 2
I now have a class called formEventClass as suggested by David. Contents of the class are:

Option Explicit

Public WithEvents tb As MSForms.TextBox
Private Sub tb_Change()
    UpdateWorksheetValue (tb)
End Sub

But when I make a change in any given text box, cells are not updated (as per David's suggestion, I've removed the call to UpdateWorksheetValue in text box onChange event. Cells are not updated even when I tab out of the field. As this is working for David, I suspect I am missing something here

Maybe show some [mcve] of your code so it might be easier to assist. What sort of "field" are you using ("fields" are not controls available on User Forms, and the answer may vary whether it's a TextBox, ComboBox, ListBox, etc...). — David Zemens, Aug 17 '18 at 13:05
_But when I make a change in any given text box, cells are not updated_, where do you create and assign `formEventClass` objects? — BrakNicku, Aug 17 '18 at 15:14
Hi @BrakNicku, I've added that as a new Class module and named the class as `formEventClass` as per David's instructions — Zac, Aug 17 '18 at 15:20
That is not enough, now you have to create for example a `Collection` and for each `TextBox` create `formEventClass` object, assign textbox to its tb property and add that object to collection. You can do it in userforms `Initialize` event. — BrakNicku, Aug 17 '18 at 15:27
@BrakNicku: Ahh, didn't know that. I can create the collection in `initialise` form, but could you point me in a direction where I could find how to create `formEventCalss` object for the boxes in collection and assign them to it's tb property? — Zac, Aug 17 '18 at 15:33
[here](https://stackoverflow.com/a/10131002) you can find this approach in action with buttons on sheet, you have to adapt it for textboxes on form. — BrakNicku, Aug 17 '18 at 15:36
Guys, thanks for your help. This is something new for me and was well worth learning with your help. I will implement this solution on Monday as drinks await now.. but thankyou for the help — Zac, Aug 17 '18 at 15:41

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

If you want to get fancy using `WithEvents`...

Create a Class Module and name it tbEventClass. Put the following code in this module.

Option Explicit

Public WithEvents tb As MSForms.TextBox
Private Sub tb_Change()
    Call UpdateWorksheetValue(tb)
End Sub

This defines a custom class (tbEventClass) which is responsive to the events of it's tb property which is a TextBox. You'll need to map your textboxes to instances of this class during the form's Initialize event:

Public textbox_handler As New Collection
Private Sub UserForm_Initialize()
Dim ctrl As Control, tbEvent As tbEventClass
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "TextBox" Then
        Set tbEvent = New tbEventClass
        Set tbEvent.tb = ctrl
        textbox_handler.Add tb
    End If
Next

End Sub

Important: You will either need to remove or modify the Change event handlers in the UserForm module to avoid duplicate calls to the "update" procedure. If the only thing going on in those event handlers is the call to your update macro, just get remove the event handlers entirely, they're fully represented by the tbClass. If those events contain other code that does other stuff, just remove or comment out the line(s) that call on your update function.

Update:

This is working for me with the controls within a MultiPage and required ZERO changes to the implemented code above.

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 17 '18 at 13:23

David Zemens

53,033
11
81
130

Hi David, thanks for the response. I've added the function that is called form each `onChange` event of every field that is added (form has all types of fields, textboxes, comboboxes, options .. etc). So I'm already using the `onChange` event. I was just wondering if there was a way to update the cells without using `events`? the way it stands at the moment, I have to update `onChange` event and `onEnter` event for each new field added. I was hoping that there would be a way around that? – Zac Aug 17 '18 at 13:31
There's no such thing as either onChange or onEnter event on a MSForm.TextBox. Without using events is simply not possible. You could reduce it by adding a button to the form, which the Click event would loop through all text boxes and update values respectively, but that still relies on the button's Click event. Or you could update it when the form closes (but that's also an event). Or you could attempt to update them from a separate procedure called from a button the worksheet (you guessed it: also an event...). – David Zemens Aug 17 '18 at 13:37
Unfortunately I have inherited this workbook :). I wouldn't have designed it this way to start with :) – Zac Aug 17 '18 at 13:53
That's exactly what I was looking for David. Unfortunately it doesn't work for me. I suspect it might be because the fields are not on the form. The way my predecessor designed this was: he has a form, then has `Multipage` on top of the form and then all fields are on the `Multipage` control. Using your approach, I did try to set the class to look for all `TextBox` fields but it doesn't want to play (`Public WithEvents tb As NewClientForm.MultiPage1.TextBox`). Is that even possible? – Zac Aug 17 '18 at 14:23
Can you please revise your question to show the code you're currently using, and what specific error or problem ("it doesn't want to play" doesn't grok for me, sorry). – David Zemens Aug 17 '18 at 14:47
It's still `Public WithEvents tb as MSForms.TextBox`. It shouldn't matter that the text box is on a `MultiPage` or not. If you're not seeing the values on the sheet update, note that the `Change` event doesn't actually fire until the user has *finished* in the textbox, so, you have to exit the textbox (e.g., with the Tab key, or by switching focus to a different control, etc. – David Zemens Aug 17 '18 at 14:53
I'm convinced it's something I'm doing wrong as it is obviously working for you. I will try and debug to see what route is taken when I leave the field – Zac Aug 17 '18 at 15:09
@DavidZemens `Change` event is fired (at least for TextBoxes) as you type, not on exit. If only update on exit was needed - it would be enough to define ControlSource property. It is bidirectional, but works only when editing is finished. – BrakNicku Aug 17 '18 at 15:31
@BrakNicku yes that's my observation normally, but does not seem to be the case when used `WithEvents`. I'm rusty on this, will try to figure out why it's behaving differently in these scenarios. – David Zemens Aug 17 '18 at 15:35
@BrakNicku yeah I think I'm missing a step. The sheet changes I see are reflective of the ControlSource bidirectionality. Will debug & revise shortly... – David Zemens Aug 17 '18 at 15:37
I described the steps you are missing in my comments under the Q, I don't have time right now, but if needed I may post a full solution later. – BrakNicku Aug 17 '18 at 15:41
@BrakNicku thanks for clearing it up! I originally had implemented the collection (incorrectly) and removed it when it seemed like it wasn't necessary (due to the bidirectionality of ControlSource property). – David Zemens Aug 17 '18 at 15:56
David and @BrakNicku, thankyou for your help again. I have got it all working as per your instructions. I do have a further query, which I thought would be more appropriate in a new question. Would either of you be able to help? [Here is the new query](https://stackoverflow.com/questions/51930517/trigger-enter-field-behaviour-through-class-for-a-control) – Zac Aug 20 '18 at 12:16

Update cell with a value when form field is selected

1 Answers1

If you want to get fancy using WithEvents...

Update:

Linked

If you want to get fancy using `WithEvents`...