Tricky. Upon deactivating a ws, trying to launch a form.. can't pass ws name into form

Trying to create a TaskManager: create some tasks, each of which has its own worksheet. Each worksheet has a series of steps, with a Y/N drop down to check if each step is complete.

When the user leaves the page, (deactivate sheet), I want to launch a form which is populated with each of the steps with a N completion status, or if all the steps are complete, archive and delete the task.
That's all done.

The problem is that upon deactivating a sheet, the next sheet becomes the ActiveSheet, so I can't use that in my code (I want to check the values in the deactivating sheet). I've resolved that by dimming shP (sheetPrevious) as the sheet that is being deactivated, but I can't seem to pass that sheet into the form, so the form keeps collecting from the new sheet.

Here's the code for the Deactivate, which only has an error upon calling the form. (shP is a global variable, Dim on Workbook page public)

Please Login or Register  to view this content.

And here is the Form Initialize Code, which populates the form (I tried making it public too, that hasn't changed anything), ws is a public variable as well

Please Login or Register  to view this content.

Any idea how to pass in the value for shP into the form????

Hello crobins8,

Welcome to the Forum!

The only way to pass the sheet name to the UserForm is by using the UserForm's Tag property. This property is a string variable. The UserForm must first be loaded into memory before you can assign a value to the Tag. I have made the changes to your code. If you have any problems, lety me know.

Please Login or Register  to view this content.

Please Login or Register  to view this content.