Open Userform when you close a spreadsheet

Hi guys

I've got a 'Control Panel' spreadsheet which is all based on UserForms. When a user clicks a particular button, it opens one of many separate 'Regional' sheets which they work in.

When the user closes the 'Regional' sheet, I would like the 'Control Panel' sheets userforms to show again. However I can find no way of doing this as it simply switches back to the 'Control Panel' sheet without loading the forms (as I had to hide the 'Control Panel' useforms to give them access to the 'Regional' sheet).

Any help would be greatly appreciated

Cheers

Nick

Originally Posted by mudraker

Try this on the controlpanel sheet

Please Login or Register  to view this content.

Thanks Mudraker. Already tried that and it doesn't work; I tried putting msgboxes in all the Worksheet_Activate, Workbook_SheetActivate etc etc subs to see if any of them appeared when the 'Regional' sheet closed. Unfortunately none of them did.

Any other ideas?

I have two completely separate workbooks, ControlPanel and Regional. The ControlPanel consists of various user-forms, which ultimately lead to ControlPanel being hidden and the Regional workbook being loaded.

When a user closes the Regional workbook (literally quits out of it), ControlPanel comes back (being the remaining workbook open). What I can't get it to do is to run/show the ControlPanel userforms when it switches back to ControlPanel.

As mentioned, I've tried putting msgboxes in various subs to see if they are called when Regional closes and ControlPanel is reverted to, but it doesn't seem to trigger anything.

Thanks for your time, it seems to me that it's something that VBA should be triggering (eg. Worksheet_Activate sub) but isn't.

Cheers

Nick

Thanks for your help Mudraker and apologies for the sheet/workbook mix-up.

The problem seems to be that I'm closing the Regional workbook with a command button. When it reverts to the ControlPanel workbook it does not run the Workbook_Activate sub. If I simply close the Regional workbook it does run the Workbook_Activate sub.

I've checked for EnableEvents and for DisplayAlerts and they're both set to true. The macro that closes the workbook is very simple and as far as I can see should not stop the Workbook_Activate from running:

Please Login or Register  to view this content.

Any idea why the macro stops it running?

Kind regards and many thanks

Nick

Excellent, thanks. Seems so obvious when you look at it! Much appreciated.