Auto_open from Worksheet Object Code

[Stuart]

All,

I am developing a sort of disclaimer sheet that 'veryhides' all sheets
in a model on workbook_open, requiring a user to agree to a disclaimer
before the sheets are then unhidden.

The aim is to distribute the disclaimer sheet as a single worksheet
that can be copied into any spreadsheet, and work pretty much
immediately.

The problem i am hitting is i can't find a way to replicate the
workbook_open/auto_open event purely with code contained in the
worksheet object.

Q1) Is it impossible?

My fall back is to use an auto_open routine in a module, and create an
installation .xls that will

a) copy the sheet across
b) copy the module across

but

Q2) I don't know how to copy a module from one workbook to another in
code
Q3) I don't know how to check (in code) first that the destination
workbook doesn't already have an auto_open routine, or what to do
about it if it does!

ANY help on Q1,2, or 3 very very much appreciate.

Cheers,

Stu

 

[Dave Peterson]

Q1. I don't think you'll find any worksheet event that's always going to fire
when the workbook opens.

Q2. I'd start by reviewing Chip Pearson's site:
http://cpearson.com/excel/vbe.aspx

Q3. Check Chip's site for: "Searching A Module For Text"

But writing code to change code is fraught with problems. (Depending on the
version of excel...) If the user doesn't allow access to the workbook's project,
your code will fail. If the project is protected, then there isn't a reliable
way to unprotect that project via code.