Force Enable Macros - Fit very hidden with my current macros

[mikeinvirginia]

Hi all,

Thank you for all the expertise. I have a marco to clear some cells and to disable the save prompt. I need to add one more step. Force the user to enable macros. I found a few options but I am too noob to make them work. I appreciate any help to add a very hidden code to my current code.

Current - To Clear Cells & Disable Prompt

Private Sub Workbook_Open()
Application.EnableEvents = False
Worksheets("Calculator").Range("C4:C5").Value = ""
Application.EnableEvents = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Calculator").Range("C4:C5").Value = ""
Application.DisplayAlerts = False
Me.Saved = True
End Sub

Option Found for Welcome Screen & Very Hidden

Private Sub Workbook_Open()

'worksheets to show when macro is enabled

WorkSheets("Calculator").Visible = True

'worksheet that shows reminder to enable macro

WorkSheets("Prompt").Visible = xlVeryHidden

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'worksheet with reminder

WorkSheets("Prompt").Visible = True

'worksheets to show when macro is enabled

WorkSheets("Calculator").Visible = xlVeryHidden

ThisWorkbook.Save

 

[johnnyL]

One Question. How do you plan on saving your macro code if your macro code does not allow saving?

 

[6StringJazzer]

One Question. How do you plan on saving your macro code if your macro code does not allow saving?

It allows saving. It just doesn't allow saving when the file is being closed.

 

[6StringJazzer]

It looks like you already have what you need. You just need to copy the second set of code into the first set. I assume you have created a sheet called "Prompt" that tells the user that macros must be enabled to use the file.

However, you must save the workbook upon closing for your changes to take effect. Your version inhibits saving on closing.

Private Sub Workbook_Open()

Application.EnableEvents = False
Worksheets("Calculator").Range("C4:C5").Value = ""

'worksheets to show when macro is enabled
WorkSheets("Calculator").Visible = True
'worksheet that shows reminder to enable macro
WorkSheets("Prompt").Visible = xlVeryHidden

Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Worksheets("Calculator").Range("C4:C5").Value = ""

'worksheet with reminder
WorkSheets("Prompt").Visible = True
'worksheets to show when macro is enabled
WorkSheets("Calculator").Visible = xlVeryHidden

ThisWorkbook.Save

End Sub

 

[Zyndstoff]

I guess that is leading nowhere: if a user has not enabled macros, you can't run a macro to force him to enable macros, since the macro won't run if the user has disabled macro execution...

 

[6StringJazzer]

I guess that is leading nowhere: if a user has not enabled macros, you can't run a macro to force him to enable macros, since the macro won't run if the user has disabled macro execution...

I have used this technique in the past. What is happening is that you create a worksheet that has a message to the user (worksheet "Prompt" as referenced in the code):

"You must enable macros to use this file"

Then when you save the file, you set all sheets to Very Hidden, except Prompt, which is visible

When the user opens the file, the Worksheet_Open sub will hide the Prompt sheet, and reveal the other sheets. However, if macros are not enabled, this won't happen and the user just sees the prompt.

For added protection, the author should put password protection on the VBA project.

It's only good for compliant users. If someone has malicious intent they can find ways around it, even with a password.

 

[Zyndstoff]

I have used this technique in the past. What is happening is that you create a worksheet that has a message to the user (worksheet "Prompt" as referenced in the code):

"You must enable macros to use this file"

Then when you save the file, you set all sheets to Very Hidden, except Prompt, which is visible

When the user opens the file, the Worksheet_Open sub will hide the Prompt sheet, and reveal the other sheets. However, if macros are not enabled, this won't happen and the user just sees the prompt.

For added protection, the author should put password protection on the VBA project.

It's only good for compliant users. If someone has malicious intent they can find ways around it, even with a password.

OK, I see your point.
I understood the initial question that the OP was trying to find a way by means of a macro to enbale macros...