Modal UserForm becomes modeless after SaveAs

[CyberJar]

Hello,

During system testing of my Excel 97/VBA, I have discovered the following bug:

I have a modal UserForm which controls the Active Worksheet (which is underneath the UserForm). I click a Save button to copy & save the Active Worksheet into another Workbook.

Here's the bug: When control returns back to my App, the UserForm is still there but in a modeless state. I can access the Worksheet - scroll, type into cells, etc. This must not happen, the Worksheet must always be controlled through the UserForm.

How do I set control back to the UserForm and disable direct access to the Worksheet after returning from the SaveAs routine?

Thank you,
CyberJar

[RobDog888]

You could probably trap for this in the Workbook_Activate() event
and handel it some way to restore the modality of the userform or
close it and re-open it.

HTH

[TheFIDDLER]

Can you post your code, I tried but am unable to duplicate this error. My control stays with the form and does not shift to the sheet. Using XL97.


btw:
You might also want to protect your sheet via a password such that if control is passed to the sheet, that the users can't do any damage.
And where your form modifies the sheet, unlock and lock the sheet for the changes only.

[CyberJar]

Hey There,

Thanks for your replies.

I was able to solve this by explicitly setting the Worksheet to Activate.

After Downloading, the last statement was:

MyWorksheetObject.Activate


And this did the trick! Focus returns to the controlling UserForm .

Thank You,
CJ