Setting worksheet as apublic varaible

[tonygg9]

Hi. I have a userform that copies data from one worksheet to another. I have used the following code in the initialise userform to set names for each sheet

Please Login or Register  to view this content.

This works fine but I have other actions on the userform like changing a value or saving changes. These cant use shSource or shDest as they are not set publically in the userform. How can I change them to make it useable across the whole userform. I have tried using public but the "" around "sheet2" and "sheet1" mean it cant work. I guess there is something simple I am missing

Thank you.

[YasserKhalil]

Insert a module and declare public variable in the module as follows:

Please Login or Register  to view this content.

Instantiate this public variable in the application load event:

Please Login or Register  to view this content.

Now you can refer the worksheets with the shSource and shDest variables

[romperstomper]

If they only need to be available throughout the userform, add:

Please Login or Register  to view this content.

to the top of the userform code module, before any routines, and make sure they are not declared elsewhere.

[tonygg9]

Many thanks