Message on screen before close / save

[ErikJan]

I have a WB with code. When opened without macro's enabled, I'd like to make that clear.

So in AutoClose, I enable a picture (read: make it visible) with that message. My problem is that I do NOT want to show that picture during the prompt to save the sheet (as it is meant to only show up when opening the sheet without macros [and the AutoOpen will remove the message]).

I tried "Application.ScreenUpdating=False" but that doesn't prevent the screen from showing. How do I deal with this?

Are there other ways to somehow enable a message before close that shows when a sheet is opened without macro's?

PS. I'd like to stay away from BeforeSave and AfterSave events as that might mess up with OneDrive AutoSave (which, I know, can be switched off in the sheet)

[HansV]

I'll be watching this thread because I don't know of a way to do that.

[StuartR]

I have seen something done in the BeforeClose event, but I don't have a copy now.

[ErikJan]

Thanks, I did some tests, this is what fires (in order) if a WB opens:
WB_Open
WB_Activate
WB_WindowActivate
Auto_Open

And this if a WB closes:
WB_BeforeClose
Auto_Close
WB_BeforeSave
WB_AfterSave
WB_WindowDeActivate
WB_DeActivate

The only thing I can think of (if I don't want to use BeforeSave and AfterSave) is to check if the WB needs to be saved in the Auto_Close and then check the same in the WB_WindowDeActivate or WB_DeActivate.

If the first is NO and the second is No, the WB was not saved and I can exit
If the first is NO and the second is Yes, the WB was saved and then I can protect the WB and save again
If the first is Yes and the second is No, the WB was saved and then I can protect the WB and save again
If the first is Yes and the second is Yes, the WB was not saved and I can exit

Could that make sense?

[ChrisGreaves]

I have a WB with code. When opened without macro's enabled, I'd like to make that clear.

Do I understand the nature of the problem?
If macros are NOT enabled at the time the project is opened, then we can't execute macros when the project is opened.
Therefore we need to set up some sort of visual clue at the time the project is closed (therefore hours, days, or years before the project is opened).

If at the time the project is opened macros ARE enabled, then our enabled macros can nullify that visual clue without the user seeing it.
But if at the time the project is opened macros are NOT enabled, then the default visual clue set in place when last the project was closed will remain in effect, and at the time of opening, the user will be warned.

Did I get that right?
Thanks
Chris

[ErikJan]

@Chris: Indeed. So the only way to create a visual clue is to do that when the sheet is saved the time before it is opened.
That is what I'm looking for here.

[Doc.AElstein]

Here’s a solution, or idea for one….
The solution is not perfect, and has a few quirks*** which I don’t quite fully understand yet. I expect it may not do what’s wanted completely. But it might help give some more ideas towards developing a full working solution

The solution requires another workbook, OpenWatcher.xls . This extra workbook must have been opened previously and that must have macros enabled. ( I have got a check on OpenWatcher.xls to see if macros are enabled in OpenWatcher.xls. If they are not enabled then it will tells you about it… )
*** A quirk that I don’t understand fully yet is that it only seems to work properly if I get to this typically later macro warning prompt situation , which usually comes sometime after you have been using excel for a few minutes.
Initially you seem to have a slightly different prompt situation like this . The solution does not seem to work too well when you are in that initial . situation.
### I got an initial workaround for that quirk…

The Solution
So this solution will only work if previously workbook OpenWatcher.xls is open, and has macros enabled. You must do that first.
The solution then only works consistently if you are in this warning situation .

Once that workbook, OpenWatcher.xls, is opened , then if you open the other test file , ToEnableOrNotEnableThatIsTheQuestion.xls , without macros enabled, then a simple message box will tell you that. (If you open the file , ToEnableOrNotEnableThatIsTheQuestion.xls , with macros enabled then nothing happens.)

This is what is going on, ( as I understand it )
The way it works basically is that an object gets made, WotchaOpen , which is Set to the Excel Application and importantly also, this object also has all the event coding of the Excel application, one of which is the _WorkbookOpen event. ( Here we are talking about any workbook that gets open, not a specific one )
So a _WorkbookOpen macro in OpenWatcher.xls , ( specifically WotchaOpen_WorkbookOpen ) , checks a flag in a spare worksheet in the test file, ToEnableOrNotEnableThatIsTheQuestion.xls , to see if that flag indicates that macros are enabled. If that flag is set to indicate that macros are enabled then it does nothing apart from resetting that flag to indicate that macros aren’t enabled.

When you open the test file, ToEnableOrNotEnableThatIsTheQuestion.xls , if
_ you enable macros then a Workbook_Open in ToEnableOrNotEnableThatIsTheQuestion.xls sets the flag to indicate that. So then WotchaOpen_WorkbookOpen will do nothing apart from resetting the flag ( to indicate macros are not enabled ) ready for the next time..
_ On the other hand if you don’t enable macros then the flag indicator stays indicating macros are not enabled, and then the WotchaOpen_WorkbookOpen will tell you that, that is to say give the warning.

It appears that provided you are in this typically later macro warning prompt situation ,as opposed to that slightly different initial prompt situation l , the Workbook_Open macro in ToEnableOrNotEnableThatIsTheQuestion.xls is done first before the WotchaOpen_WorkbookOpen macro in OpenWatcher.xls.
So what is happening is that if macros are enabled in the test file, then just before WotchaOpen_WorkbookOpen checks the flag indicator , the flag indicator gets set to indicate that macros are enabled.
( The flag indicator I have arbitrarily as text in the first cell of a spare worksheet, GlobyFlagelations. That flag indicator is either set to
OopsMacrosNotEnabled
or
EnabledMucros )
_.__________________

What to do to test all this
Just to make it clear what you need to do to test all this....
First open OpenWatcher.xls and make sure that macros are enabled.

Then, my test file, ToEnableOrNotEnableThatIsTheQuestion.xls is the file that would be opened with or without macros enabled. If you open it and don’t enable macros then you will get the warning message

But , to work consistently you must be in this typically later macro warning prompt situation rather than the initial situation
So it’s all not really a good solution yet, but it might give someone else some ideas

Alan

_:_________

P.S. 1 I did an alternative version of the file with the extra coding in it, OpenWatcherProObfuscation.xls. This does the same basic thing but is the more professional way of making it more confusing with Class modules .

P:S: 2 ### The workaround to get this rather than rather than this , which I hit by chance, was to do a SendKeys to open the VB editor.


_.____________

Ref
https://excelfox.com/forum/showthread.p ... ion-Events

[ErikJan]

Thanks Alan. Your problem might work but (with respect), to me it merely seems to move the problem of macro's being enabled or not to another sheet (as if one doesn't enable macros in OpenWatcher, this still won't work...).
I'll try my idea in my lost post above in the coming days and will report back as well. And yes, your approach might indeed 'trigger' something... let's hope that.
PS. Another way would be to create a (personal) VBA digital code-signature, sign the code and then import the signature on the end-user system. Macro's can then be set to run if the code is signed with a trusted signature. I've done that before but it requires (one time) actions on remote systems.

[Doc.AElstein]

Hi

... it merely seems to move the problem of macro's being enabled or not to another sheet (as if one doesn't enable macros in OpenWatcher, this still won't work......

Correct , I agree fully… I thought that might be the case. I thought the point of the exercise might be to check and warn of non enabled macros , so yes, I agree with you , my solution just moves the problem somewhere else. I thought that might be the case.

_.__________________

A minor improvement
We can improve slightly the check on the macros in the OpenWatcher.xls to make it more obvious, for example by having a warning shape.
The uploaded file, OpenWatcher2.xls shows what I mean.

http://i.imgur.com/vnOwS0j.jpg
http://i.imgur.com/wTp5vG1.jpg

Watcher2 Warning.JPG

You will only see the shape, if at all**, very briefly on closing the workbook.(** possibly you may never see it due to a bug (Ref ###)

So this minor improvement makes it less likely that OpenWatcher___.xls would not be opened without macros enabled. Note also , that the full solution does not strictly speaking rely on macros being enabled in OpenWatcher___.xls : If the user does not enable macros in OpenWatcher___.xls , then the user is instructed to do so: The user is told to close OpenWatcher___.xls and then re open it , remembering to enable macros.


However, I still don’t like any of my solutions, generally, they are messy, and inconsistent in working.
But they might just possibly trigger some other thoughts, that’s all

Good luck, and if I think of anything else I will re post

Alan


_.__________

Ref ###
http://www.eileenslounge.com/viewtopic.php?f=30&t=29579

_.______________________________-

P.S. For the sake of clarity, and to help anyone else seeing this Thread, it might be worth you clarifying exactly what you want, because the following IS satisfied by a simplified file,
Requirement:
……we need to set up some sort of visual clue at the time the project is closed (therefore hours, days, or years before the project is opened).
If at the time the project is opened macros ARE enabled, then our enabled macros can nullify that visual clue without the user seeing it.
But if at the time the project is opened macros are NOT enabled, then the default visual clue set in place when last the project was closed will remain in effect, and at the time of opening, the user will be warned.
Solution:
SimplifiedFile.xls

I do not think that SimplifiedFile.xls is what you want.
But it might help us all if you clarify why SimplifiedFile.xls is not what you want

[ErikJan]

Thanks, some observations:

• You saved the file as XLS, why not XLSM?

• You use "LET" in variable assignments, why? (note, I'm just trying to understand if I missed something special here)

• Seems to me the ClsFlag is not needed. You use it as 'memory' but if you use "Application.EnableEvents = False" always in the BeforeClose event, the close won't call itself anyway

• Using your tool leaves my Excel in an 'undefined' state if I close it (there is a scree but it's all grey); I need to close in Task manager (but that can be my system...)

• The "SendKeys" opens the VBE editor; I don't want that for end users

• The BeforeClose still changes the sheet before closing and I can see that

Overall, your tool seems to do exactly what I did when I started this thread:

"So in AutoClose, I enable a picture (read: make it visible) with that message. My problem is that I do NOT want to show that picture during the prompt to save the sheet (as it is meant to only show up when opening the sheet without macros [and the AutoOpen will remove the message]).

I tried "Application.ScreenUpdating=False" but that doesn't prevent the screen from showing. How do I deal with this?"

Also there, I couldn't avoid seeing the updated screen before close (just like you).

I thought about my proposal suggested earlier (AutoClose & Wb_DeActivate events) but I think that won't work. My next thoughts are going towards disabling AutoSave (I believe that can be done in VBA and then use the Before- and AfterSave events anyway to resp. place and remove my warnings on the sheet. I'm afraid however that also there, the screen update issue will remain (the updated screen shows while the save dialog is displayed).

Bonus: a last strange move in my brain thought about another sheet, program or script that could open the closed XLSM file, 'unzip' and set the visibility of the picture on the sheet to true. I suspect that will be some XML setting. That way I can manipulate the file to always show the picture on opening regardless of how the file was saved. Of course that would require the user to still DO something before opening the file so I discarded that thought

[Doc.AElstein]

Hi
I think I have confused a bit with all my postings. Sorry about that
Also, I am not sure which solution your last post is referring to?
_ The simplified solution which involves just one file , SimplifiedFile.xls
_ The full solution which requires two files, the watcher file and the end user file, (my example file, ToEnableOrNotEnableThatIsTheQuestion.xls is equivalent to the end user file )
_.___________________

. I will try to clarify again. Sorry if it confuses further…


_1) ………..Why .xls
Just personal choice. Usually most things I pass in .xls fils can be saved and used normally as .xlsm files.
I like some of the older stable Microsoft software and only use the newer Office versions when I have to.
It is easier for me to develop things in lower versions and then move upwards. Usually then there is no compatibility problems. Developing things in newer versions and then needing to use them occasionally in earlier versions can sometimes give compatibility problems.

_2 )………… Why use Let
Just personal choice. You have not missed anything special.
2 reasons really:
:2.1) Main serious reason: To distinguish from Set. I like as much structure as possible:
I am not sure if I have it right, I may not be using exactly the correct terminology, but my thinking is that you can broadly/ approximately speaking do three things to/with stuff/things in VB
_2.1(i) You assign an object , which VB knows to do if you use Set
_2.1(ii) You assign a value , which VB knows to do if you use Let
_2.1(iii) You use a Method/Property/Function of something , which VB knows to do if you don’t use anything
But because VB is good at guessing/ distinguishing between (ii) and (iii) you can leave it out in cases (ii), and VB will use Let “internally” for you as the implicit default in whatever compile thingy it does/has.
I prefer not to use implicit defaults, and I like as much structure as I can get.

_2.2) Aesthetics.
My coding distinguishes itself, I feel, from others, in its beauty. My coding is beautiful. Those extra pretty blue Lets further add to its beauty, IMHO :)
2.2) is not a serious reason :)

_3 …. Seems to me the ClsFlag is not needed. You use it as 'memory' but if you use "Application.EnableEvents = False" always in the BeforeClose event, the close won't call itself anyway
In my macro the Application.DisplayAlerts = False prevents any possible warning pop up that delays the workbook saving. ( I typically would otherwise get one telling me that I am using a .xls file in compatibility mode. There may be no warnings or different warnings depending on which system and file extension you use. )
In most cases, as you say, Application.EnableEvents = False in the macro prevents it calling itself.
But in this case, the code line of .Close calls the macro again: Microsoft have a built in “watcher macro” that monitors the closing of a any workbook. I think that then calls the Public Sub Workbook_BeforeClose( at some “higher level”. That is not influenced by our Application.DisplayAlerts = False.
So in this case that extra check involving ClsFlag is needed.

_4) ….Using your tool leaves my Excel in an 'undefined' state if I close it (there is a scree but it's all grey); I need……………
I am not sure what is going on there. It might be that all excel files are closed, but the instance of Excel is still opened. I am not sure

_5) …The "SendKeys" opens the VBE editor; I don't want that for end users
I mentioned that this was the first workaround I found to get this rather than rather than this , which I hit by chance. There may be another workaround.

_6) ….The BeforeClose still changes the sheet before closing and I can see that
I am not sure what you are saying there. I think there may be some confusion between us as to exactly what you want. I expect this may be one of those things where we only fully understand what you want after you have already a full solution doing exactly what you want. Or we may be sometimes talking about the different thing and confusing ourselves.
If you are talking about the simlified solution, then yes, you do see the prompt. But you only see it very briefely, and you can't respond to it, because immediately after that the file is closed.
In my main solution involving the extra watcher file, the end user file does not have any BeforeClose macro.
In my main solution involving the extra watcher file, the end user file only has one single macro,- Just this one:

Code: Select all

 Private Sub Workbook_Open()
 Let ThisWorkbook.Worksheets("GlobyFlagelations").Range("A1").Value = "EnabledMucros"
End Sub

( any other macros and stuff in the workbook are part of my experimenting and they are not used in the solution )

It is difficult in a written media like the internet to get the message so clearly across. I have probably confused the issue a bit with all my postings. Sorry about that.
_.________________________________________________




_... I have hit that damn post limit again ..... continued in next post

[Doc.AElstein]

_.... continued from last post


Overall, your tool seems to do exactly what I did when I started this thread:
"So in AutoClose, I enable a picture (read: make it visible) with that message. My problem is that I do NOT want to show that picture during the prompt to save the sheet (as it is meant to only show up when opening the sheet without macros [and the AutoOpen will remove the message]).

Once again, I am not quite sure what you are saying there. I am not sure what you are referring to.
It is possible that I have not explained my original solution idea well enough, and so you have not fully understood my solution that involves the extra workbook, watcher.xls or watcher2.xls
In that solution, once the watcher workbook is open and macros are enabled for the watcher workbook , then I think my solution does exactly what you want.
For example, when you save or see the prompt to save your file ( my example file, ToEnableOrNotEnableThatIsTheQuestion.xls ) , you will never see any warning massage.

The warning message associted with my watcher solution is a message box: This one.

Warming in Watcher Solution_.JPG

You will only ever see that when you open your file, ( my example file, ToEnableOrNotEnableThatIsTheQuestion.xls ) without macros.
If you open your file and enable macros then you will never see that warning.
You will never see that warning at the prompt to save a file.
The end user file has no working coding other than the Private Sub Workbook_Open().
My Watcher solution has no coding anywhere doing anything when you close the end user file

But I think we both agree / understand that you do not want a solution requiring that any workbook must have macros enabled. So as it is my, watcher solution is not any use. I thought from the start that it might be, but thought it might help give some other ideas.

_.________________


I try to summarise again for clarity:
My example file, ToEnableOrNotEnableThatIsTheQuestion.xls , is equivalent to your end user file.
But my watcher solution requires that previously the watcher file is opened with macros enabled previously.
My watcher solution effectively has an “external system ” monitoring the opening of end user files, and it tells you if you open a file and don’t enable macros.
The snag is that to get that “external system ” up and running , you must get the watcher file opened once with macros enabled for that watcher file.
A possible solution could be one that gets this “external system ” up and running without any end user action. I will take a geuss that this could be done. But I personally have no idea how to do it.





Alan

[ErikJan]

Warning if no macros.xlsm

OK, I tried to show what I mean but in my mock-up this seems to work fine.

Take a look

PS. the code has two subs only: AutoOpen (2 lines) and AutoClose (1 line).

[ErikJan]

And to your proposal Alan, I do appreciate that but the 'external system' / 'second WB' suffers from the same problem: this will only work if the sheet is executed with Macro's Enabled. If I could make that happen, that would solve my problem in the first place already and I wouldn't need a second 'system'... right?

[Doc.AElstein]

And to your proposal Alan, I do appreciate that but the 'external system' / 'second WB' suffers from the same problem: this will only work if the sheet is executed with Macro's Enabled. If I could make that happen, that would solve my problem in the first place already and I wouldn't need a second 'system'... right?

Correct, if your problem in the first place is to have a solution that could be applied in a single workbook to warn if that workbook was opened without macros enabled. I originally expected that was the case, but I was not 100% sure.

[Doc.AElstein]

Warning if no macros.xlsm
OK, I tried to show what I mean but in my mock-up this seems to work fine.
Take a look

That looks like a good solution!

( Possibly if you wanted to be sure that the user does not delete that shape, then you could somehow add protection , like

Code: Select all

Option Explicit
Sub Auto_Open()
 ActiveSheet.Unprotect
 ActiveSheet.Shapes("MyPicture").Visible = False
 Range("A1") = Now() 'Make a change so a close WB will trigger a save prompt
End Sub
Sub Auto_Close()
 ActiveSheet.Shapes("MyPicture").Visible = True
 ActiveSheet.Protect
End Sub

One last thing that I am still unsure about is whether the user should be able to modify and save changes to the workbook even if the user did not enable macros )

( One other very minor thing that sometimes confused me a bit is that you often referred to things like “saving the sheet”. I don’t think you can save a sheet independently. You can only save a workbook. But I think you always meant save the workbook?

[ErikJan]

Thanks, yes, protecting the sheet is an extra step (but in my original code I had already implemented that so for this problem I left that out).

You are right: with "save the sheet" I mean "save the workbook" ;-)

Of course my problem still isn't solved entirely for two reasons: (1) In my original code, I already see the image while the Save dialog is up (oddly enough, that doesn't happen in my example. Guess I'll have to do some more tests/optimizations to fix that.) and (2) somehow I'll still have to do the same (read: 'show the picture') if the user FIRST manually saves the WB and then closes the WB. That is relevant as if I ALWAYS save on close, that might backfire... what if the user messed-up the content somehow and wants to exit without saving?
So maybe disabling auto-save and then using the Save-events is the only way out after all... (and yes, I realize that my question was for a way to prevent that).

[ErikJan]

PS. Here's the story from MS on AutoSave: https://docs.microsoft.com/en-us/office ... and-macros

[Doc.AElstein]

....(oddly enough, that doesn't happen in my example.

That might be due to the Excel Bug I mentioned here

...possibly you may never see it due to a bug
......Ref ###http://www.eileenslounge.com/viewtopic.php?f=30&t=29579..

But I am not sure – that bug is inconstant. That bug sometimes prevents shapes showing at the time that they should. That might be what you are seeing in your example

_._________________________________

You may likely find that these workarounds result in you seeing the shape as expected. ( I realise you don’t want to see the shape. I am just adding this information for completeness)

Code: Select all

 Sub Auto_Close() ' Workaround for Shape display bug
 ActiveSheet.Shapes("MyPicture").Visible = True
 DoEvents: DoEvents '   Workaround:    http://www.eileenslounge.com/viewtopic.php?p=229098&sid=e8e8000959dfd2145e05df93dc3a264c#p229098
End Sub

There are lots of other workarounds: We find that many things stop the bug from appearing. So in your original coding you probably were coincidentally doing something that stopped the bug.

Here just one example of something else that stops the bug:-

Code: Select all

Sub Auto_Close() ' Workaround for Shape display bug
 ActiveSheet.Shapes("MyPicture").Visible = True
 ActiveCell.Activate '   Workaround:    http://www.eileenslounge.com/viewtopic.php?p=228877&sid=b3edebb8782269aaf5829f36497b3d1e#p228877
End Sub

_.____________________________________

If you want to try to make use of this bug to your advantage, then you may find doing that code line, ActiveSheet.Shapes("MyPicture").Visible = True , at the very end may always result in the bug.
If that does not consistantly result in the bug, then we may be able to find some other way to ensure that the bug always occurs.

Possibly such a solution might be considered to be in appropriate since you are relying on a Bug. On the other hand, Microsoft often refer to such things as “features” rather than “Bugs”.
So if anyone questions such a solution you can simply reply to them … “….my solution takes advantage of Microsoft’s useful feature which prevents a shape being shown in certain situations…. “

:)

_...when is a bug not a bug ? ..... when its a Microsoft "feature"

[ErikJan]

Right; my problem is that I DO NOT want to show "changes that are only for the file to save". They should remain invisible during close or until I undo them in the AfterSave event