Force Users to Enable Macros in a Workbook

[ChristianPetersen]

I am sorry, if this already has a thread. It is the first time I am logged on to MREXCEL.com. I have previously found MREXCEL VERY useful. Now to my question (sorry for my poor English):

I would like to "Force Users to Enable Macros in a Workbook". I have seen solutions to this problem on many sites, including this one. However, it seems to me that to work the user must accept macros, otherwise how can the macro run that forces the user to accept macros. Put differently, if the user just ignore 'accept macros' and save the file in the usual XLSX format, he or she can use the spreadsheet without macros?

The reason why, I need users to enable macros is quite simply that the spreadsheet is only valid for a specific period of time, and then closes down. However, if the user disregard macros they can continue using the spreadsheet for ever (which is a long time ).

I appreciate any help on the issue.

Best regards
Christian

 

[gallen]

It's a little tricky but possible.

In essence you add an opening sheet that when the workbook is opened it is the only sheet that is visible. And a button that unhides the sheets using VBA. As the sheets will be hidden via VBA they will be 'veryhidden' meaning they can't be unhidden using the menus

The tricky part is the saving.

You have to override the saving of sheets so they can't use the manual save button. Then when the user is saving, you ensure all sheets are 'veryhidden' except the opening sheet.

I'm sure if you google it you will find the code.

 

[ChristianPetersen]

Thanks for your very quick reply.

I am a little puzzled, hope you can help me again, thanks. I understand that I add an opening sheet that when the workbook is opened it is the only sheet that is visible. So far so good. However, does this mean that I need to use properties for each sheet (except the one, the users sees) to make them 'very hidden' before I make the spreadsheet available. And when, if the user accepts macros, I use VBA to make the remaining sheets visible?

 

[alansidman]

Here is a step by step tutorial

http://www.datapigtechnologies.com/flashfiles/forcemacros.html

 

[ChristianPetersen]

Thanks .

 

[ChristianPetersen]

It works charm. BUT there is an issue with my VBA:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Unhide the Warning Sheet (that macros must be enabled)
Sheets("WARNING").Visible = True
'Step 2: Hide the other sheets (for example DATA)
Sheets("DATA").Visible = xlVeryHidden
Sheets("ANALYSIS").Visible = xlVeryHidden
Sheets("REPORT").Visible = xlVeryHidden
End Sub

Private Sub Workbook_Open()
'Step: Reverse "BeforeClose"
Sheets("DATA").Visible = True
Sheets("ANALYSIS").Visible = True
Sheets("REPORT").Visible = True
Sheets("WARNING").Visible = xlVeryHidden
End Sub

If the user accept macros, save the file and when open it again and this time save it as a 'normal' XLSX file, he or she is able to use the Excel file and can see all the sheets (e.g., analysis) that ought to be hidden?

Any help will be highly appreciated. Thanks.

 

[ChristianPetersen]

I need to add. The above problem exist, if the user after having saved the file as an XLSX file - including the data he has entered and when closes the program and do not save it, the sheets will not be hidden.
In other words I probably need to force him to save it as an XLSM file?

Thanks

 

[Fluff]

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If SaveAsUI Then
        MsgBox "You cannot SaveAs, only Save"
        Cancel = True
    End If

End Sub

This should allow users to Save, but not save as

 

[ChristianPetersen]

Thank you very much for your incredible fast reply. I wish you a wonderful evening.

 

[Fluff]

Your welcome