AUTO_Open 2

[voetj]

How can I run the AUTO_OPEN macro when an Excel appl was opened while pressing the SHIFT-Key

 

[RobBroekhuis]

Hit alt-F11 to go the VBE, then type in the immediate window (at bottom right of your screen - type ^G if it's not there)
call auto_open

Rob

 

[voetj]

Hi Rob,

Actually I mean In my application I've an AUTO_OPEN routine.
When the SHIFT KEY is pressed while opening the application, the AUTO_OPEN will be disabled.
I want to check if the SHIFT key was pressed and if so call AUTO_OPEN.

 

[RobBroekhuis]

If shift is pressed, then the automatic macros will not be executed. There's no way around that. What you can do, is use a public variable, say,

public AutoOpenRun as boolean

in your AutoOpen sub, put

AutoOpenRun=true

Now you can check in other macros that your user may call whether the AutoOpen sub has been run yet, and if it hasn't, call it:

if not AutoOpenRun then Autpen

Does that help? Rob

 

[xlbo]

Or - the standard workaround is that the auto_open macro unhides all sheets except for a front sheet that says "You need to enable macros to use this workbook"

the sheets would be xlveryhidden on workbook close. As xlveryhidden sheets can only be unhidden thru code, this makes a very effective little protection routine to make sure that macros are enabled and the auto_open macro is executed Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[voetj]

Geoff

Can you give an example ?

 

[xlbo]

eg
In the before_close event
For each sh in thisworkbook.sheets
if sh.name <>&quot;MacroDisabledFrontPage&quot; then
sh.visible = xlveryhidden
else
sh.visible = true
end if

In the auto_open sub
sub auto_open()
for each sh in thisworkbook.sheets
if sh.name <> &quot;MacroDisabledFrontPage&quot; then
sh.visible = true
else
sh.visible = xlveryhidden
end if
other code goes here
end sub

If the wb is opened with shift held down then the auto_open will not run and therefore, all sheets except the sheet that tells the user that they need to enable macros are hidden. ie the workbook cannot be used

If the user does not press shift then the macro executes and all relevant sheets are unhidden (and the info sheet is hidden)

Basically, it renders the workbook useless unless the auto_open macro is allowed to run

You should also protect the VBA project so that they cannot get into the VBE and unhide the sheets via code Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur