Automatic message

[richzip]

Is there a way of setting up a message, that will display in a small pop-up
window, every time someone opens a specific Excel workbook? the user would
have to click OK to actually open the document.

Thank you.

 

[Gord Dibben]

Yes there is a way but why not just password the workbook?

User would have to enter the password to open.

But to give you what you ask for.

Private Sub Workbook_Open()
X = MsgBox("Click OK to access this workbook", vbOKCancel)
If X = vbCancel Then GoTo endit
Exit Sub
endit:
ThisWorkbook.Close , Saved = True
End Sub

Copy/paste into Thisworkbook Module


Gord Dibben MS Excel MVP

 

[ker_01]

If your users do not enable macros, you may find yourself having to do some
complex (and generally unsatisfying) workarounds. However, if your users
always enable macros when opening your workbook, you can use the following
solution.

Otherwise, my best suggestion would be to make your data sheet veryhidden,
so if the user doesn't enable macros they can't reach the data (at least not
the casual user), so they will re-open the file with macros enabled, and then
see your message. That will involve incorporating code to show/hide sheets,
and use the workbook_close event to re-hide everything when closing.

To get the message showing in an macro-enabled workbook

In the workbook_open event, add the following line of code:

msgbox "this is the message"

To find the workbook_open event:
1. open the VBE by pressing Alt-F11
2. on the left side, look for a panel called "Project - VBA Project". If you
don't see it, press Ctrl-R
3. Find your workbook, and expand the subfolders until you see a line called
"ThisWorkbook". Double click it to bring up the workbook code window.
4. In the workbook code window, at the top, change the left drop-down box
from (General) to Workbook
5. It should default to Workbook_open and insert a line of code that says
"Private Sub Workbook_Open()" and another line below that says "End Sub". If
so, just paste that one line of code (above) into your workbook. If the line
that was inserted 6. is Workbook_[something else] then go to the top right
drop-down box and select "Open", then go back to step 5, find the correct
code block, and insert the line of code.

HTH,
Keith