Fun - Ask user if they want to edit (on a timer) before running workbook_open

[mvidas]

Hi everyone,

I was presented with an interesting question yesterday, and have had a lot of fun getting it working nicely. They had a workbook that is called out of windows scheduler with a workbook_open event in it, but wanted the ability to have users edit the workbook without holding shift to suppress events if they wanted to. This prompts the user (for x seconds) to see if they want to edit it, and if no one is around (at 3am when it is scheduled) it will proceed after the specified waiting period. If a user had opened it they can choose to edit the workbook before the event proceeds.

Might be a bit too involved to make a kb entry out of it, but it might prove useful to someone. I'd love it if anyone came up with an alternative way to do this or had any improvements/suggestions to the current one; I just wish I had an actual use for it as opposed to creating it for someone for free :)
As stated, suggestions/improvements welcome!
Matt

[Jacob Hilderbrand]

Nice idea, another way I can think of is to take the code out of the workbook people want to edit and have the software open a second workbook that the user will not use, then that workbook can have Open code that will open the other workbook and run whatever code you want on it.

[mvidas]

Good idea (makes a bit more sense...), though the above was what was wanted to keep everything in one file. The best idea, IMHO, is to convert the workbook_open to vbs and just have the scheduling script make all the changes. But this way the OP got it done the way he wanted, so they're happy.
Seems to me I remember a different reply when I read my email notification text :) I was getting ready to reply with "I have users?"

[Bob Phillips]

Another way

[mvidas]

Hey Bob,

I've used the popup method in the past in a couple vbscripts, but strangely it doesn't work like it should in my vba. I just let it sit there for about 60 seconds before I decided to click ok. Could be my copy though, I have a couple other things that should work but don't (mine is slowly corrupting I think--at least one of those things is the office assistant so I dont have to deal with him coming back after i told him to stay away)

[Bob Phillips]

Hey Bob,

I've used the popup method in the past in a couple vbscripts, but strangely it doesn't work like it should in my vba. I just let it sit there for about 60 seconds before I decided to click ok. Could be my copy though, I have a couple other things that should work but don't (mine is slowly corrupting I think--at least one of those things is the office assistant so I dont have to deal with him coming back after i told him to stay away)

Matt,

I have heard other people say that this method of timing out doesn't always work. It hasn't happened to me but it doesn't necessarily mean that it is a problem your end. Shame because it is incredibly simple.

There must be an OnTime way of doing it too, but that is asynchronous of course, so maybe not.