toggle from EXCEL form to EXCEL worksheet and back.

[zabo]

I have need to lock a worksheet from user update. I am providing a form for any updates. I want to be able to type in my changes on the form and click a command button that will update my changes. Once the updates are complete, I want the form to be nonmodal so that I can scroll around on the sheet to view the effects of my change. I am finding that I cannot pass focus from the form to the sheet and back again. It is as if the form is loaded modally. I thought of perhaps not loading the form until my user attempts to make changes to the sheet. A protected sheet shows a modal dialog box upon such attempt that tells them they cannot make changes to the protected sheet. If I could ride on that code and upon closing of that box open the form I have mentioned earlier, then by pressing the update button on the form will not only update the form, but also close the form so that the user can navigate around the sheet. Any attempts to change the sheet will again bring up Excel's standard warning message followed by the user form again.

Any help would be greatly appreciated.

Thanks,
Zabo.

 

[rmikesmith]

Zabo,

I don't know of any way to hook into Excel's internal code. The closest thing are the exposed event handlers, with which you can often preempt and even abort Excel's built-in behavior. What version of Excel? Versions below 2000 do not have modeless userforms. I am using v2000 and can scroll about the worksheet with a userform showing, select cells, etc. If you are using v2000 or greater, be sure the ShowModal property is set to False. If you are using a version less than 2000 then the best you can do is to close the form after changes are made. Do you have an objection to this?

HTH
M. Smith

 

[zabo]

rmikesmith,
Thanks, I tried to do that but must have gotten the syntax wrong. After reading your post, I returned to that and played around a bit more, got the syntax right and viola' worked like a charm. I actually like that better. I didn't really want to get into overriding Microsoft's warning system anyway.
I appreciate the help.
Zabo.