Force VBA/Macro prompt to re-appear

I have some workbooks on a shared folder with VBA and Macros. Some of my users have opened the workbooks but not clicked on the "allow Macros", or possibly cancelled the option.

Is there any way to force the prompt to appear again so all users can enable the code, as at present little errors are appearing in submitted reports making it look a little untidy

Hi Ali

No we are all using Pro Plus 16

I have found this

http://www.cpearson.com/excel/EnableMacros.aspx

But it is throwing me up a
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Ambiguous name detected: Workbook_BeforeClose
---------------------------
OK Help
---------------------------

on

Please Login or Register  to view this content.

Probably means you already had a BeforeClose event and now have two.

Depending on their settings some users might not get a prompt at all, so making the files unusable unless the macros are enabled is probably your best option with possible alternatives being storing them in trusted locations, or digitally sign them and becoming a trusted developer.

https://web.archive.org/web/20090309...uru.ca/node/91

https://support.microsoft.com/en-us/...a-98505ecd1c01

Hi Byte

Yes, I already have both a "Private Sub Workbook_BeforeClose(Cancel As Boolean)" and a "Private Sub Workbook_Open()" in my workbook.

I was starting to have a look at why I was getting the error....

Is there any way of combing my existing code in those subroutines and web.archive code you linked?

Code I am currently using is

Please Login or Register  to view this content.

I would create separate subs and then call them from the Workbook events.

The principle is that when you save/close the workbook it'll make all the sheets "very hidden" apart from one title page where you can leave a remark that macros need to be enabled.

If the macros are enabled, then the sheets are unhidden and the title page is hidden.

Some things to consider are:
If you include the "Hiding" code in the Save event, then every time your user saves the sheets will disappear.
If you include it in the Close event, then you'll have to force a save in order to hide the sheets and save that state before closing.

Hi Byte

That is absolutely sublime. Thanks for the guidance.

One issue I have is that I want one of the worksheets to stay permanently hidden, but even if I change it's properties to xlSheetVeryHidden it still gets unhidden. is there any way to omit a single worksheet from the code, or to force it to be hidden?

You could change the "ShowAllSheets" routine like this.

Please Login or Register  to view this content.

I have edited it as below. Is that the correct way of doing it?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I'd probably do it like this and check that the WelcomePage and Validation sheets exist before showing them.

Please Login or Register  to view this content.

Hi Byte

I have tried to modify your code to all both SHOW and HIDE both work

the following code

Please Login or Register  to view this content.

Keeps giving Error 1004 Method 'Visible' of object'_Worksheet' failed where i have marked <<<<<<<

You need to unhide the other sheets before hiding welcomepage- at least one worksheet must always be visible.
Workbook protect needs to be removed during the process.

Hi BM

I can fully understand WHY both of these would need to be the case, but I am just chasing my tail at the moment.

Whatever i try and alter int he code for Hide/Show Sheets, an error is thrown. Are you saying that a second HideAllSheets would need to be called, or that the current one would need splitting in to 2 sections?



I know how to turn protection on and off with the use of

Please Login or Register  to view this content.

, but am unsure of where it would need ot be placed. I would hazard a guess that it would be just before

Please Login or Register  to view this content.

?

I've made some adjustments here.
Hiding when workbook is closed rather then saved.
Separate sub to remove/apply workbook protection
Also for the break links.

Originally Posted by ByteMarks

I've made some adjustments here.

You are a gentleman and a scholar.

Would i need to make similar changes to the ShowAllSheets? Because I assume this is the exact opposite of HideAllSheets?

You're welcome.

As the Introduction sheet is to the far right you don't need to make any changes because the other sheets will be shown before that one is hidden.
If it was the first sheet, you'd need to unhide the others before hiding it.

Hi BM

I am going to start over with this workbook as it was getting a bit messy with all the changes.

Is there a logical way you would work with regards to adding VB and Macros?

It's not too bad.
Looking at it you have the same worksheet change event in nearly every sheet, so you used the ThisWorkbook_SheetChange instead. That would mean one routine to handle all the sheets.

You probably don't need 3 modules.
There are some unnecessary subs e.g. "Workbook_Open2"

The show/hide can be combined in to one sub

Please Login or Register  to view this content.

Hi BM

I have used ThisWoorkbook in the past - not sure why I din't think about using it again this time.

To simply things I have done away with the DataValidation worksheet, and added the data I need for this to the bottom (rows to be hidden later) of "Sheet1", as I know this sheet will always be visible and accessible.

I have renamed my worksheets so that when I use the "Sort_Tabs_Alphabetically" my "01EnableMacros" will always be the left most sheet.

If I am going to move my VB from individual Sheets to ThisWorkbook, is there anything I need to keep in mind? i.e. do I need to add "If sh.Name = ActiveSheet.Name Then" or anything/something else?

do I need to add "If sh.Name = ActiveSheet.Name Then" or anything/something else?

Yes, you'll likely need to assess the sheet unless you want the code to run on every worksheet.
"Select Case" is cleaner than "If Else" IMO for doing that.

Do I need to tackle each piece of code individually? Or do i need to be looking at ALL of the code? or is it a bit of a mix depending?

i.e. i am looking at moving "AutoFilter_Example1" and "Worksheet_Change" to the "ThisWorbook" area, as both of these pieces of code will run on every worksheet created.

I can understand what each of these pieces of code does, but not sure how to move them.

AutoFilter_Example1
This looks to all of the cells in A12:A199 (specified in "Worksheet_Change"), looks to see if the cell contains "Hide" and if it does, it filters the column. Either side of the code, the Protection is turned off and back on.

Worksheet_Change
This sets the range of cells where data is likely to be, and specifies what range "AutoFilter_Example1" should use.
It also checks the value of H8, and depending on the result places the answer (Q) in A9 - so in essence almost a Data Validation (H8) and embedded IF (A9)



I assume these cannot be combined as 1 is a Sub, the other a Private Sub

You have done too good a job at protecting the workbook

The Sheet name cannot be edited because the WS is protected.

I have the following Macro

Please Login or Register  to view this content.

but it will not run because it cannot rename the sheet to "2nd Sheet" - that name is another issue to be covered in another post

I have tried adding the following:

Please Login or Register  to view this content.

hoping that in the same way that "Private Sub Workbook_SheetChange" does not run run on "Introduction" or "DataValidation"

Am i using the wrong code? or have I just put it in the wrong place?

The Sheet name cannot be edited because the WS is protected.

I made a mistake in the fileProtection sub in post #23 which I've corrected.

You should then be able to use:

Please Login or Register  to view this content.

hoping that in the same way that "Private Sub Workbook_SheetChange" does not run run on "Introduction" or "DataValidation"

Already taken care of in the change event itself.

Please Login or Register  to view this content.

Originally Posted by ByteMarks

Already taken care of in the change event itself.

Please Login or Register  to view this content.

Does that mean that whatever code is added to "ThisWorkbook" will not be applied to "Introduction" or "DataValidation" because of the code above? So any code for those worksheets would need to be added to them directly?


For future reference, how would you go about adding code to all worksheets if you had this applied? Or would it be just as the case above - adding it manually to those sheets omitted by the "Then Exit Sub" routine?

Does that mean that whatever code is added to "ThisWorkbook" will not be applied to "Introduction" or "DataValidation" because of the code above?

Currently yes. If the sheet is "Introduction" or "DataValidation" it runs Exit Sub which quits the routine.

You could easily expand the if or use a Select Case to do different actions on those sheets within the same event.

I have added the "Call fileProtection you edited above, but it returns

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Sub or Function not defined
---------------------------
OK Help
---------------------------

with "Sub Add_New_Briefing()

Call fileProtection(False," highlighted

I am making changes to Sheet Names so will post the new book shortly.

Does Sub fileProtection look like this?

Please Login or Register  to view this content.

Yes, exactly the same.

Workbook attached with new sheet names.

Sub fileProtection needs to go in the module

DOH!

Next steps are colours of tabs and the Forcing of Macros. Did you want me to open another post? or happy to carry on here?

Carry on. This thread is about the forcing of macros

"Sort_Tabs_Alphabetically" has stopped working.

I am pretty sure I know why - because the "Protect Workbook" is enabled - I assume by "Sub fileProtection"

The sheets will not re-order because if I try to manually drag them I get the "National Speed Limit" looking icon, and renaming returns "Workbook is protected and cannot be changed"

Is it the "With ThisWorkbook" that is causing the issue?

Does this work for you?

Hi BM

This is pretty much spot on, thank you. There is only 1 strange anomaly. Whenever you try and create a new worksheet, every other sheet errors at the following point

Please Login or Register  to view this content.

I cannot see any rhyme or reason for that happening.

The reason was that adding the sheet trigged the calculate event which then reprotected everything.
In the attach file I've disabled events before making these changes and also got fid of the calculate event entirely. Instead the tab colour is changed via the change event.

That is bob-on. Thank you for all of the help.

Great to hear. You're very welcome.