That's it. I've had enough!!!!

[Gordon]

Folks...

My laptop is about to fly out my office window. I've been trying for 3 days
to programme a page which shows when macros aren't enabled and the sheets
that I want users to use when macros are enabled. It's the old chestnut of
how to get round excel security.

For £50 (payable by paypal) can a really clever boffin out there fix this
for me. Must be someone I've spoken to before on this. It would take you 15
mins tops!

Reply either in this forum if you're interested or via my email address
(e-mail address removed).

Save me!!!

G

 

[Bernie Deitrick]

Gordon,

Copy the code below and paste it into the Thisworkbook's code module NOT a regular codemodule.

Name the page that you want to show when macros are disabled "Macros Disabled", and then save the
workbook. (or change "Macros Disabled" in the code to the name of the sheet). The code will change
which sheets are show whenever the workbook is closed - of course, it is not foolproof, but it is
pretty good.

HTH,
Bernie
MS Excel MVP


'Start copying code here


Dim boolSelfClose As Boolean
Const ShtName As String = "Macros Disabled"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mySht As Worksheet
If boolSelfClose Then Cancel = True
Sheets(ShtName).Visible = True

For Each mySht In Worksheets
If mySht.Name <> ShtName Then mySht.Visible = False
Next mySht

Application.DisplayAlerts = False
ThisWorkbook.Save
boolSelfClose = False
ThisWorkbook.Close
Application.DisplayAlerts = True

End Sub


Private Sub Workbook_Open()
Dim mySht As Worksheet

For Each mySht In Worksheets
mySht.Visible = True
Next mySht

Sheets(ShtName).Visible = False

boolSelfClose = True

End Sub



'End of copied code

 

[Gordon]

Hi Bernie...

I did as you outlined. Lots of issues. Can I send you the file?

Cheers

Gordon

 

[Chip Pearson]

See http://www.cpearson.com/excel/EnableMacros.aspx
and http://www.cpearson.com/excel/EnableMacros2.aspx

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[Gordon]

Thanks Chip...

Same advice as on the other string [auto enabke macros]. No worries, I'll
keep plugging away and see if I can make it work. So frustrating!!!!

Cheers

Gordon.