Write data to Excel cells, update formulas, and then continue with updated values

Question

I am working on an interactive Excel project.

I have a series of UserForms that take user-entered data and writes them to cells.
In the final UserForm in the sequence, data is written to a final cell.
In another cell, the data should concatenate (not using VBA, but as a standard formula in the cell using CONCAT).
Then, the VBA code should copy the concatenated cell to another one.

The problem is, all the VBA code from all the UserForms that have been used is executed, and then the CONCAT in the cell kicks in.
This results in the cell being copied before the data has been written to any cells and the CONCAT formula has returned its results.

This is the existing code:

Private Sub IssueComp_Done_Click()
    If IssueComp_Yes.Value = True Then
        Range("Answers!B23").Value = "I have verified the matter."
    Else
        Range("Answers!B23").Value = "I have not verified the matter"
    End If
    Range("Front!B7").Value = Range("Answers!C25").Value
    Dim objLoop As Object
    For Each objLoop In VBA.UserForms
        If TypeOf objLoop Is UserForm Then Unload objLoop
    Next objLoop
End Sub

This is the line that copies the concatenated cell:

Range("Front!B7").Value = Range("Answers!C25").Value

This is my CONCAT formula:

=IF(A52="DISP",CONCAT(C2,CHAR(10),CHAR(10),C7),IF(A52="NONDISP",CONCAT(C2,CHAR(10),CHAR(10),C20),"WAIT"))

When the code is run, cell Front!B7 displays WAIT.
This is how I knew it was copying the cell before values had been written and concatenated.

I replaced the source cell with the first cell that should contain data and it is empty when that line of VBA is executed.
This demonstrates nothing is written until the VBA code has executed to the end.

I found code to sleep, and to use the Application.CalculationState property.
It doesn't copy anything.

I tried rewriting the code to use variables instead of just cells, and again got nothing.

Is there a way of making sure cell Answers!C25 has executed the concatenation, i.e., no longer displays WAIT, and then execute the VBA code that copies the values?

In the forms properties is ShowModal set to True or False? ShowModal property — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented May 29 at 7:11
Your formula I believe is in cell Answers!C25. Now I checked the formula. It is not referencing the cell Answers!B23. So how is the cell in C25 supposed to update? From Answers!C2, Answers!C7 or Answers!C20? — Siddharth Rout
– Siddharth Rout, Commented May 29 at 10:46
@DarrenBartrup-Cook This was it! This has solved the issue without any waiting, sleeping etc. I didn't even know this was a thing. You've just allowed me to barrel forward on developing my Excel app, and I'm extremely grateful. Thank you! — youngstubbs
– youngstubbs, Commented Jun 1 at 17:37
@SiddharthRout Thanks for the comment, but that formula was fine. It turned out to be the ShowModal property that needed setting. — youngstubbs
– youngstubbs, Commented Jun 1 at 17:40

Dave Lett · Accepted Answer · 2025-05-29 10:16:44Z

0

I recommend that you change your formula while you're testing/building your solution so that you can eliminate simple mistakes like the wrong value being A52.
=IF(A52="DISP",CONCAT(C2,CHAR(10),CHAR(10),C7),IF(A52="NONDISP",CONCAT(C2,CHAR(10),CHAR(10),C20),"WAIT: " & A52))

After that you can follow Harold's guidance to force a recalculation before copying the data. You can use Application.Calculate or you calculate on the specific cell (shown below).

Range("Answers!C25").Calculate
Range("Front!B7").Value = Range("Answers!C25").Value

And there is certainly no harm in adding the 'Wait briefly section that Harold has in his snippet.

answered May 29 at 10:16

Dave Lett

842 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

youngstubbs Jun 1 at 17:39

Thanks for the answer. It turns out it was the ShowModal property stopping things working as desired.

Harold · Accepted Answer · 2025-05-29 10:46:02Z

0

EDIT

If the below still does not work, then it will never work. The reason is that the VBA code calls the value of the cell, which then triggers a calc because the cell contains IF and CONCAT, so it will show WAIT...always.

The best solution is to move the formula IF and CONCAT to the VBA code to do. This way, it all executes as expected. Granted, your VBA code needs to be a bit beefier now.

--------------------

You are writing values to B23 and then immediately reading C25, but Excel has not yet calculated the formula in C25.

You must force Excel to recalculate, wait, then read the value.

    ' Force recalc
    Application.Calculate

    ' Wait briefly
    DoEvents
    Application.Wait Now + TimeValue("0:00:01")

    ' Now read the value
    If Range("Answers!C25").Value <> "WAIT" Then
        Range("Front!B7").Value = Range("Answers!C25").Value
    Else
        MsgBox "Formula did not update in time.", vbExclamation
    End If

edited May 29 at 10:46

answered May 29 at 3:36

Harold

511 gold badge2 silver badges8 bronze badges

1 Comment

youngstubbs May 29 at 3:53

Thanks, Harold. Frustratingly, though, that still copies across WAIT, even when I set the TimeValue to 5 seconds. In fact, I've just tried it set to 10 seconds and the result is the same. It's pausing the VBA code as expected, but doesn't seem to be releasing processing to the Excel formulas.

Collectives™ on Stack Overflow

Write data to Excel cells, update formulas, and then continue with updated values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related