Check for enables macros

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
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?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,974
Office Version
  1. 365
Platform
  1. Windows
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

Code:
.Visible = xlVeryHidden
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
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

Active Member
Joined
Jun 20, 2018
Messages
322
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

Active Member
Joined
Jun 20, 2018
Messages
322

ADVERTISEMENT

bump
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,974
Office Version
  1. 365
Platform
  1. Windows
I do not understand what your problem is :confused:

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? :confused:
 
Last edited:

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,974
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 20, 2018
Messages
322
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 .
 

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,615
Members
413,917
Latest member
devansh02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top