Activating Worksheet after Loading Modeless UserForm

This should be an easy one. I have a userform that I load in modeless form in the Workbook_Open event. All I want to be able to do is to activate the worksheet after the userform is loaded. Here is my current procedure:

Please Login or Register  to view this content.

The modeless userform is still activated after the procedure is run.

TIA

Jason

Jason

When you open a userform I'm pretty sure code control passes to it.

So your code to activate the workbook might not ever being reached.

Thanks for the quick reply, Norie.

So you're saying it's not possible? I also tried including that line of code in the UserForm_Initialize and UserForm_Activate event handler procedures to no avail.

Jason

I'm not saying it's impossible I'm just trying to suggest a likely reason why what you have doesn't work.

I've not had time to test but I would of thought it should work in the userform events you mention.

I'll do a little testing tomorrow, it's Friday night after all.

But I have to ask why you want to activate the worksheet.

Friday night? You must be east of the Atlantic.

I am creating a template that requires 2 command buttons to add/delete additional pages as needed. And based on user input, I have determined that including them on a modeless userform would be the best route (I prefer a custom menu, but that's another story). When the users open the template, I would like them to be able to simply move the cursor as normal, rather than having to manually activate the sheet first.

Thanks again for your help.

Jason

Thanks for the input, Mike.

As I indicated above, I have tried this in 3 different event handler procedures: Workbook_Open, UserForm_Initialize, and UserForm_Activate.

Any additional input would be appreciated.

Jason

Hello jasoncw,

Norie is right that control passes to the UserForm. If you want to return control to the worksheet it can only be done after the UserForm_Activate event has finished, like clicking on another control. If the form isn't being used, why are you showing it? You can load the UserForm into memory without showing by using Load UserForm1 in the Workbook_Open event.

If you want to be able to have the UserForm available (visible), and get it out of the way, you can change the UserForm to behave like a window by adding miniminize and restore buttons on the UserForm Caption. Here is macro to do that.

Please Login or Register  to view this content.

Example
This will add both buttons to the UserForm. Now, the user can control it like a regular window.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Hi, Leith. Thanks for that code. However, that's not exactly what I'm looking for. Let me explain a little further.

I have a template that is basically a 1-page form. However, depending on the different jobs, the form could be several pages long. I want to be able to give the user the ability to add or delete pages to the end of the file on demand. As stated above, I would prefer to do this in a menu, but based on user input, a modeless userform would seem like a better idea.

So when the template is opened, it is ready to be used (the userform is not needed). However, when the user gets to the end of page 1, they should have a command button (or other control) that can add a page to the end. That is the purpose of the modeless userform. So when the file is opened, I want the userform loaded and visible, but control passed to the worksheet.

I hope that makes sense. Not sure if this is possible based on the above posts, but it's worth a shot.

Thanks again for any input.

Jason