How to Create a Pop Up Message Box Upon Opening Excel File

[rommelq]

Hi. Can anybody help me on how to create a message box so that anytime the excel file is opened, the message box will pop up and only upon clicking the "OK", it would disappear. The message I want is " This is only a tentative database. "

And also i want the prompt to appear before the excel file closes to remind the user again.

Thanks.

 

[agihcam]

Hi -

During opening

Private Sub Workbook_Open()
MsgBox "This is only a tentative database.", vbInformation + vbOKOnly, "Message from Rommel"
End Sub

Before close;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "This is only a tentative database.", vbInformation + vbOKOnly, "Message from Rommel"
End Sub

 

[rommelq]

Thanks agihcam for your reply. I went to macro and clicked Visual Basic Editor. Then i insert a module and posted the codes. Then i saved it. Unfortunately, it didnt work when i tried to open the file. I enabled the prompt "enable macros" but still nothing. What could be the error?

 

[agihcam]

hhhhmmm. those codes should be at thisworkbook module not on the standard module. double click thisworkbook from VBA project to the left then paste the codes on the large white screen to the right.

 

[rommelq]

it did work! thanks a lot, agihcam. Enjoy the rainy day in our country.

 

[rommelq]

Hi, I have another follow up question. What if i want to put a cancel button at the closing message box so that the excel file won't close? What codes should i put?

 

[agihcam]

try;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("This is only a tentative database.", vbInformation + vbOKCancel, "Message from Rommel") = vbCancel Then
Cancel = True
Else
Cancel = False
End If
End Sub

 

[rommelq]

it worked! thanks once more!