make Named Range & EVALUTE() calculate while the UserForm is in progress

**carsto** · 04-18-2018, 12:20 PM

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.

**Keebellah** · 04-18-2018, 05:09 PM

Hi, I've red and reread your question and... well, honestly, I can't really make heads or tails off it.
The only suggestion I can offer is that when one userform is active it will remain on the foreground unless you set modal to False then you can click around it and do other things, the same with userform 2 and now I think I might have lost you and given you a similar story that will not make heads or tails sense to you.

If vbModal is set to True the userform takes lead and nothing else can be done unless you hide it and initiate another routine that has to return to that userform show it again and the close it, if you don't do this it will stay in memory and disrupt things.
I suggest you look it up in Google, search text something like: vba excel processing two userforms at the same time
Happy coding

**carsto** · 04-18-2018, 06:08 PM

I figured it out.

I had set the WORKSHEET to EnableCalculation = False at workbook open, so Application.Calculate wasn't calculating that sheet.

So it has nothing to do with userforms or the Excel 4 EVALUATE() function... Just a lame-brained programmer!