It seems to me that every Monday now, my macro no longer works in the office. Outside of the office, such as my home or the hotel, the program works. This past Monday, it started out with my inability to access the internet. My machine just would not go to our vendor's site that we normally use for reporting. And my Outlook, I think it shut down itself. Usually when my machine acts persnickety like that, something is going on in the background, such as a Microsoft update. Except when it came time to shut off the machine at the end of the day, Windows never gave me the message that Windows was going to do an update.
But it did do an update. After going through the Control Panel and Programs and Features the next day, I found that Microsoft did do some kind of update, maybe the licenses to all of the Microsoft products I had on my machine. It was very weird. And VERY AGGRAVATING. On Monday I had to run the program at home after I arrived home and the macro worked. I even brought my work machine home and the macro worked on my work machine. So either there is something about the network at work that is preventing me from running my macro as usual or all of those Windows update are consuming machine energy or memory.
On Tuesday morning, I tried running the macro at work again ... and it worked. A tech guy came by on Wednesday and we started to have problems with the macro. I think it was because the macro was using a directory path suited for my home machine and thus the effort messed up my machine at work. So when we used the correct directory path, the macro still would not work right.
Sigh. The tech guy never did figure out why I'm having problems with my macro. He said it could be because there was some background maintenance work going on. It did feel like there were little gremlins in my machine on Monday.
When my program runs, I don't know at what stage the program is so when I have to force Excel to shut down, I don't know where the problem is. I don't know what part of the program worked and what part is hanging up. So I decided to add a message box telling me what step the program is working on. I couldn't use the normally MsgBox command because that message will wait around until you hit the OK button. I wanted something that would give a message and then disappear after so many seconds.
Here is a sample macro that one can use when one wants to display a message for so many seconds and then go away. I first tried it for 10 seconds and it didn't seem to work, so I tried at 1 second and it worked. I think 10 seconds will feel long, especially when your message is very short, so some experimentation will be necessary for each message.
Dim Acktime As Integer, InfoBox as Object
Set Infobox = CreateObject("WScript.Shell")
'Set the message to close after 1 second
AckTime = 1
Select Case InfoBox.Popup("Clearing from tabs prior week data (Step 1)", AckTime, "This is your message box",0)
Case 1, -1