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

G

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
[email protected].

Save me!!!

G
 
B

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
 
G

Gordon

Hi Bernie...

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

Cheers

Gordon
 
G

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.
 
C

Charlotte E.

Make a small UDF (User Defined Function), something like this:

<---
Public Function MACROFLAG(Optional FlagValue As Long = 0)
MACROFLAG = FlagValue
End Function
--->

Then, in the key formulas of your spreadsheet, add this new 'formula/value'
to your current formulas.

Example:

=SUM(A1:A10) becomes =SUM(A1:A10)+MACROFLAG()

Now, if ever a user opens the spreadsheet without macros enabled your
formulas with show up as errors, but work just fine, if macros are enabled
:)

You can even use this to test for macros enabled, and return a message to
the user, if you write something like this in a cell:

=IF(ISERROR(MACROFLAG());"You have to enabled macros!";"")

or toggle between message and formula:

=IF(ISERROR(MACROFLAG());"You have to enabled macros!";SUM(A1:A10))

In fact, the use of this method is endless, since you can use MACROFLAG() to
set factors in your formulas, just to make sure formulas doesn't work,
unless the user enable macros, like:


=(A1/B2)*100 becomes =(A1/B2)*MACROFLAG(100)


Hope this helps...
 
Top