Check for enables macros

[wmtsub]

I ran into a problem where some users do not have their macros enabled. I found several old posts about hiding pages and keeping one visible with a warning / instruction on how to enable macros. But I could not get any of them to work on office 365. Does anyone have a solution where I can definitively check to see if macros are enabled?

 

[Yongle]

Consider this simple trick

- add another worksheet to the workbook (the landing sheet)
- save the workbook with the original sheets hidden in such a way that they cannot be unhidden without VBA
- Workbook_open contains an instruction to unhide those sheets (and hides the landing sheet)
- if macros are not enabled then the user cannot see or access anything other than the landing sheet
- need to ensure that the sheets are hidden whenever the file is saved, and immediately unhidden if the workbook is still open

.Visible = xlVeryHidden

 

[wmtsub]

Yes , that is what I tried. Here is the macro. Placed in Thisworkbook object, but it does nto work.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Sheets("START").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "START" Then
ws.Visible = xlVeryHidden
End If
Next ws
ActiveWorkbook.Save
End Sub
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
Sheets("START").Visible = xlVeryHidden
End Sub

 

[wmtsub]

So it seems for some reason if the macro trust is not enabled the first time then this macro works. But once you run them and turn macros - even if you then turn it off - macros seem to continue to work. I can not after running these macros turn of macros in this worksheet. Any Idea why?

 

[wmtsub]

bump

 

[Yongle]

I do not understand what your problem is

The 2 macros posted by you do exactly what you want - I tested them
The first time the workbook is opened only START is visible until macros are enabled
After that, the other sheets are always visible when the workbook opens because macros have been enabled

POST#1 - I ran into a problem where some users do not have their macros enabled

POST#4 - I can not after running these macros turn off macros in this worksheet. Any Idea why?

Why do you want to turn off macros?

 

[wmtsub]

I ran the macros on a sheet with macros disabled and the macros [as designed] did not run.
I enabled macros and ran it again and the macros ran fine.
I disabled the macros again and ran it once more - the macros still ran and they are not supposed to right?

 

[Yongle]

Why do you now want macros disabled?
That was not what you wanted when you started the thread
In answer to your final question ...
NO - you authorised macros to run on the workbook - so that is what is happening.

To move forward can you explain exactly what you want to happen from start to finish
- apologies if I have missed something that is important to you.
- what problem are you (or the user) experiencing because the workbook is set to macros enabled?

 

[wmtsub]

I was disabling macros so I could test the functionality of the procedure. I assumed if I turned it off then it would repeat the same process as before it was turned on. Seems it does not .