Pass variable between subs within module

**oOarthurOo** · 04-01-2010, 04:11 PM

Having trouble passing the current state of Application.MoveAfterReturnDirection between 2 subs, in one module, that are executed on workbookopen and beforeworkbookclose.

Code of this workbook

Please Login or Register  to view this content.

Code of Module 1

Please Login or Register  to view this content.

The MsgBox executes and provides a number -4161, which I hopes excel interprets as xIDown.

I haven't changed any of the names yet, originally I just set it to move right on open, and move down on close. Worked fine, but many people will be using this workbook and I don't want to force down on them if they had something else set.
Could use some help figuring out what I'm doing wrong.

**oOarthurOo** · 04-01-2010, 04:22 PM

Tried it somewhat differently, but still not working. Here is my second try:

Content of Module 1

Please Login or Register  to view this content.

Content of This workbook

Please Login or Register  to view this content.

**oOarthurOo** · 04-01-2010, 04:36 PM

Final try, just to show you guys that I am working on the probem. I just don't know enough about it I guess. Anyway, based on a similar question found I google I tried the following:

All of this is in my workbook, no modules exist anymore.

Please Login or Register  to view this content.

**shg** · 04-01-2010, 04:43 PM

In ThisWorkbook:

Please Login or Register  to view this content.

**oOarthurOo** · 04-01-2010, 04:54 PM

Originally Posted by shg

In ThisWorkbook:

Please Login or Register  to view this content.

Worked a treat. I can see several elements I knew nothing about ... declaring a variable as a move direction, and cancelling boolean, which I will need to read up on.

Thanks very much!

**shg** · 04-01-2010, 04:58 PM

... declaring a variable as a move direction ...

xlDirection is an enumeration (one of many in Excel), which is a list of values that a variable type can assume. xlLeft, xlRight, ... are members of that enumeration.

... and cancelling boolean ...

You can read about that in Help for the BeforeClose event.