Adding a worksheet and making it sheet1

  • Thread starter Thread starter Tim Meixner
  • Start date Start date
T

Tim Meixner

Now that my masterpiece workbook is nearly complete with 7 worksheets and pages of VBA code, I now
would like to add a sheet that is displayed only if the Security level does not allow macros. I
would like to display a message on changing the Security setting on the sheet, and it should be the
default sheet displayed initially. This page could be hidden using VBA code which is the test for
the Macro Security level...if it doesn't hide, the level is set too high.

The question is...first is this the best way to do this?

But more importantly can I add a sheet and make it the first sheet to display without any VBA code?

My thinking on this is I would need to create the Macro Security level message sheet in a new
workbook and copy the other sheets to it, thus making the message sheet sheet 1. Is there another
way to do this as I will also need to change numerous VBA code references from WorkSheets(x) to
WorkSheets(x+1)...this can be done, but then there will be more testing needed...again!

Any ideas will be greatly appreciated,

Tim
 
The bad news is that if macros are disabled, then your code ain't gonna run at
all.

So there's no test you could add.

And if macros are enabled, then you don't need to test.

But you can add that first worksheet with big red letters. ENABLE MACROS (and a
nice explanation of how to do it).

You could have your auto_open/workbook_open code hide that sheet and unhide all
the others.

And if you don't want code to activate that sheet, just save it with that sheet
selected.

Excel will remember where you left off when you restart.

=======
I think a simpler method is to create a separate workbook. This separate
workbook has one sheet that says "if you don't see your data momentarily, please
enable macros. Close this workbook and reopen this workbook."

(momentarily to allow the real workbook to open.)

But if macros are enabled, this helper workbook would have code in its
auto_open/workbook_open code that opens the real one (and closes itself to get
out of the way).
 
Tim,

I haven't really thought this through, but you may be able to get away with
this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Message").Visible = True ' must be first
Sheets("Sheet1").Visible = xlSheetVeryHidden
Sheets("Sheet2").Visible = xlSheetVeryHidden
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Message").Visible = xlSheetVeryHidden can't be first
End Sub

If you have a lot of sheets, you can use a loop to handle them, instead of a
bunch of separate statements. These procs go in the ThisWorkbook module.

It makes only the message sheet visible before it's closed, and the regular
sheets not visible. Then it reverses them on open. If macros aren't
enabled at open, the message sheet is all that will be seen. It must save
the workbook on close, or the user could save the workbook while the regular
sheets are visible, then close with no save. That may confound users who
wish to close with no save in order to toss out their changes. The
VeryHidden property causes the sheets to not appear in the Format - Sheets -
Unhide menu, preventing the user from unhiding them manually.

It's late in the afternoon, and I suspect there may be a fallacy somewhere
in this, but I'll toss it out anyway, and see if it causes any howling. :)
 
I think that's a pretty slick idea. One problem is the different file
names, which may confuse users. They may eventually begin opening the
second file directly, though it won't appear in the recently used file list.
The second file can be hidden (right click it, Properties), and the first
can still open it. Or it can be in another folder (though a Save As will
reveal its location). Maybe all of the above.

Just some stuff to think about.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net

Never try to teach a pig to sing. It wastes your time, and it annoys the
pig.
-------------------------------------------
 
I think that some variation of this is my current favorite.

Maybe even give the "real" workbook a password to open. And don't share it.

Have the "helper" workbook open the "real" one by supplying that password.

(Now, I've got a modification to make at work!)
 
Back
Top