UserForm1 gets data from User and creates Named Ranges (XYZ_1_ABC, XYZ_2_ABC, etc). XYZ_1_ABC = the user entered data, for example BLUE, XYZ_2_ABC = RED

These Named Ranges are used to populate numerous cells by another Named Range GetXYZName
GetXYZName uses the EVALUATE function
EVALUATE("XYZ_"&INDIRECT("RC2",0)&"_ABC")

RC2 contains the number to complete the NamedRange.
If D7 =GetXYZName & B7 = 2 then D7 will display RED

UserForm1 initiates Userform2

Userform2 gets data from User and creates a New XL file by copying a worksheet to a new file.

The copied worksheet can’t populate correctly if the cells containing = GetXYZName don’t calculate (populate RED or BLUE)

But NR GetXYZName using EVALUATE() doesn’t calculate until UserForm2 completes.

Adding Application.Calculate won’t cause the GetXYZName / EVALUATE() to calculate while the UserForm is in progress.

Is there any way to force the GetXYZName / EVALUATE() to calculate while the UserForm is in progress?


I do not have any application or sheet calculation turned off.