Formula Not Executing

[VB_Noob]

Hi All,

I have created an user form which takes user input data and populated to a table at the bottom of the worksheet. I use VBA to make it work.

Now I have created some formulas within the cells of the table. But the newly calculated data don't get to put in the form. I need to refresh the sheet in order for the new data to show. Is there a way to fix this?

 

[VB_Noob]

Hi All,

I've attached a file. The file basically is an user form that pulls the data from the table at the bottom and display them in a form.

But the form won't show the updated data after I updated the data in a cell of the table. For example, I change the patient name in the table. The new patient's name doesn't get displayed in the form until I re-filter the data.

How can I fix this? I may need to modify this worksheet to include calculation within the cells of the table and the new results will not get displayed in the form.

 

[Marc L]

Hi !

Normal as the event is only upon the cell N3 !
You must detect any change in the bottom table and
if the ID is equal to the cell N3 then refresh the top form …
​

 

[Marc L]

You must correct the patient ID in column A or in column S
(as patient 1001 ≠ Patient 1001 ‼) for this mod can work :
(or apply next post)​

Private Sub Worksheet_Change(ByVal Target As Range)
        W = [{"B3","D3","F3","H3","B5","B7","B9","B11","B12","B13","B14"}]
    If Target.Address = "$N$3" Then
            Application.EnableEvents = False
            V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)
        If IsNumeric(V) Then
                 V = 20 + V
            For C% = 1 To UBound(W)
                Range(W(C)).Value = Cells(V, C).Value
            Next
        Else
            If Target.Value > "" Then Beep
            Range(Join(W, ",")).Value = ""
        End If
            Application.EnableEvents = True
    ElseIf Cells(Target.Row, 1).Value = [N3].Value Then
        Select Case Target.Column
               Case 2 To UBound(W)
                    Application.EnableEvents = False
                    Range(W(Target.Column)).Value = Target.Value
                    Application.EnableEvents = True
        End Select
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Or for the ID replace the cell N3 validation area by this formula :

=INDIRECT("A22:A"&COUNTA(A:A)+10)

and clear column S …
​

 

[VB_Noob]

Hi Marc,

Thanks for posting the codes. I actually make it to read all the data in the table at each user input field. So it kind serves as a worksheet refresher whenever the user changes a data in the input field. It seems to be working. I just created the refresh codes in a sub procedure and call it from each data input sub.

 

[Marc L]

As the need is to just update the cell change, what my code does …​