Refresh Screen Display

Hi all,

Using Excel 2013 64-bit.

How can I refresh the screen display at run-time to the user can see new sheets that have been added and possibly click on a cell if they choose to do so?
The code I have tried so far does not update the display before offering the prompt so the user may choose a different cell

Please Login or Register  to view this content.

Full code below
Thx
w

Please Login or Register  to view this content.

MsgBox is modal; when dismissed, the code resumes running.

You could instead use Application.InputBox with type:=8 to enable the user to select a cell.

Thanks shg,

I guess I thought since all of the refresh stuff came before the MsgBox that the screen would update before the first msgbox
I commented out the first msgbox - same result.

I do have the Inputbox inside the Select Case of the msgbox.

Any other ideas?
thx
w

Please Login or Register  to view this content.

Thanks shg,

Still not updating so that I may select a cell from the control worksheet.
Revised code below in case I did not add your code correctly.

thx
w

Please Login or Register  to view this content.

The revised code below in case I did not add your code correctly

You need to turn screenupdating back on first.

Thanks shg,

I thought some of the other methods would update the screen display?
No matter, the screen display now updates - thanks.

I took out the MsgBox and Do Loop because they seemed redundant, the InputBox seems to be enough.

However I am receiving a Type Mismatch error at this line

Please Login or Register  to view this content.

In my test I chose, "Massachusetts Mutual Life Insurance" and during debugging that is the value of sCriteria.
What could cuase this error?
I get the same error message with or without the Redim statement which I feel is not needed in this case.

thx
w

Full code:

Please Login or Register  to view this content.

The Array function returns a Variant; it can't be assigned to string array, only a Variant.

Please Login or Register  to view this content.

Thanks shg,

Works great!
Also learned a couple of new things from your code.

Thanks
w

You're welcome.

That should be ...

Please Login or Register  to view this content.