Excel VBA 2010 - Problems with Variables - Option Explicit/Public

[b4tmast]

I'm in the process of a new program. My problem is that I'm trying to write so that a user is prompted one time (at set-up) for a variable - a path. After that they should never have to state it again. What I am experiencing using Option Explicit/ Public is that when I close and open the spreadsheet, it looses the variable and I have to re-do the 'one time only' InputBox. What am I doing wrong? I'm totally new at this... Thanks in advance.

Please Login or Register  to view this content.

If when I open the spreadsheet, I get prompted that it can't find the word doc. However, if I run the InputBox and put in the path variable, it will run fine until it crashes somewhere else. Is there a way to keep that variable in there 'forever'? So that when they close and open the program they don't have to keep using the InputBox? Thanks folks... Look forward to hearing from you.

[Norie]

All variables will be destroyed when you close the workbook.

If you want to keep the value you'll need to store it somewhere.

Then the next time you open the workbook you can check if there is a stored value.

If there is you can set the variable to that value, if there isn't you can prompt the user for the value.

[b4tmast]

Thank you for a solution - simple and easy. What do you think would be best used? Some type of if then upon opening, like an auto-open or upon initializing?

Thanks again.