Supressing MsgBox poop-ups from VBA

[Chrisso]

Hi All

I have added a MsgBox message to my system on Workbook_Open to display
a message if the user has opened the system in read-only. The message
just warns them that they cannot make changes. This works fine.

I have now written a report generator which opens the main system in
read-only mode to collect the data. The problem is that my report
generator also gets the read-only message and therefore waits for me
to click "OK".

How can I supress this message from my report generator code yet still
have it pop up for a normal user?

Application.DisplayAlerts = False does not solve the problem as my
message is a MsgBox and not an Excel alert.

Any ideas? Thanks in advance,
Chrisso

 

[JW]

You can set your username in Excel to something unique and then use an
If statement to check for it before displaying the message box.
Something like
If Application.Username <> "something_unique" Then MsgBox("Whatever")

You can change you username by going to Tools-->Options and selecting
the General tab. Please not that there are other ways of doing this
as well.

 

[Jon Peltier]

I'd suggest setting any other kind of flag. Messing with the username might
have unexpected consequences. Wouldn't it be funny to view a cell comment
and have "something_unique" appear in the status bar?

For example, set some kind of global boolean to true. Or put a dummy text
file named "OmitMsgBox.txt" in the program directory only on your machine or
when you're running in auto mode. Use Dir() to test for the presence of the
file. If it's there, then don't show the message box.

- Jon

 

[Steve Yandl]

One option would be to use the popup method of the windows script host shell
object instead of a MsgBox, allowing you to set a timeout for the window.
What I've got below will produce what looks almost identical to a regular
message box with an OK button and a timeout of 5 seconds if the user doesn't
take action.

____________________________________

Set wsh = CreateObject("WScript.Shell")
intRet = wsh.Popup("Opened as Read Only", 5, "Notification", 0)
Set wsh = Nothing
____________________________________

Steve

 

[JW]

Jon, you make a good point. But, I wasn't meaning to really use
"something_unique" as the name. He could use "John_A_Smith" (using
his real name of course) or something of that nature.

 

[Jon Peltier]

There are a lot of ways to approach this. Hard-coding a specific username
limits the behavior to the one user, which may be okay, but if someone takes
over the project, you have to find all hard-coded strings, and if you want
to allow someone else to use the modified behavior, you have to make the
comparison more complicated, looking for either John Smith or Jane Doe as
username. Using an external dummy file is pretty easy. You could also store
the setting in an INI file, the registry, or some other location, so the
program could check whether or not to do something. You know the little
checkbox on some dialogs, "Don't show this again"? That's how the program
known not to show this again.

- Jon