Enable or Disable Macros Dialog Box 2

[metalboy]

Hi all,

I am using a piece of vba code to log the users who use a confidential spreadsheet. The only problem being is you get the option to disable macros which stops the logging. is there any way to disable the dialog box so macros are auto enabled?

Regards to all....

“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[dyarwood]

A standard work round is make all sheets very hidden and add a blank sheet and have that visible on start up. Then in the workbook open vba let the other sheets be visible. That way the only way someone can see the other sheets is to enable macros. (Also password your vba code or someone could unhide them from that.)

 

[metalboy]

Is there any other way at all of doing this? or could you give me a detailed version with code?

Sorry i am a beginner....

Regards



“It is a mistake to think you can solve any major problems just with potatoes.”
Douglas Adams

 

[dyarwood]

Unfortunately I have not found a way to autoenable macros without lowering security to Low (not recommended). As you will not be able to control other users computers I have found this is the best work around.

Create a spreadsheet (call it StartUp or whatever you like)

For all other sheets do go into the properties of the sheet and set the visible property to xlVeryHidden

in the


Private Sub Workbook_Open()
Sheets("Your first page").Visible = True
Sheets("StartUp").Visible = xlVeryHidden
End Sub

You must also make sure that the spreadsheet is closed with the StartUp page visible and all other sheets very hidden.


This will not save the workbook.

If you set
ActiveWorkbook.Close True
this will save the work book before you close.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Sheets("StartUp").Visible = True
Sheets("Your first sheet").Visible = xlVeryHidden
ActiveWorkbook.Close False
End Sub

 

[xlbo]

nope - think about it - what would be the point of macro protection if you could change it in code !!!

This is NOT a helpdesk - as such, members are expected to be able to do some research on their own and come back with pertinent questions. It is rare that someone will post an entire bit of code such as this.

Essentially, you need to:
1) Use the workbook_Before_Close event to loop through all sheets, making them "veryhidden" and unhiding ONE front sheet with a message on to the effect "You cannot use this workbook without enabling macros"
2) Utilise the workbook_OPEN event to unhide all the "veryhidden" sheets and hide the message sheet

If macros are enabled, then the code runs and the sheets are unhidden. If macros are DISabled then the code does not run and the workbook is unusable, forcing the user to close and re-open with macros enabled. "Veryhidden" sheets cannot be unhidden without going to the VBE so it is a pretty effective method


Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question