Automatic Pop-up Message

[patsy]

I need to know how to create a pop-up message when a worksheet is opened. I
need it to say "PUT IN REQ # AND DATE NEEDED NOW". I already have the
following VBA Code to add the current date:

ActiveSheet.Unprotect
Range("T10") = Now()
ActiveSheet.Protect

ActiveSheet.Unprotect Password:="password"
Range("T10") = Now()
ActiveSheet.Protect Password:="password"

What code and where does it go to add the pop-up box?

I would appreciate any help possible.

Thanks.

 

[Luke M]

This needs to go on the ThisWorkbook sheet. Also of caution, does the
workbook only have one sheet? If not, you might want to include the line
"Sheets("SheetIwant").select" near the beginning.


Private Sub Workbook_Open()
msgbox "PUT IN REQ # AND DATE NEEDED NOW"
ActiveSheet.Unprotect
Range("T10") = Now()
ActiveSheet.Protect

ActiveSheet.Unprotect Password:="password"
Range("T10") = Now()
ActiveSheet.Protect Password:="password"
End sub

 

[Gord Dibben]

You cannot "open" a worksheet.

Do you mean "activate" or "select" a worksheet?

Right-click on the sheet tab and "View Code"

Paste this into that module. Note: no error-checking included.

Private Sub Worksheet_Activate()
reqno = InputBox("Enter a Requisition Number")
ActiveSheet.Unprotect Password:="password"
Range("T10") = Now()
Range("U10") = reqno
ActiveSheet.Protect Password:="password"
End Sub


Gord Dibben MS Excel MVP

 

[patsy]

That didn't work. This is a workbook with several tabs. Each sheet in the
workbook is password protected. The guy who opens this workbook has to put a
requisition # and the date that he needs the order on the req. He always
forgets to enter into these fields. I want to make this idiot proof for him
so that when he opens the workbook and goes to the worksheet that needs this
information, the pop-up box will tell him that he needs to put this
information in. When I added the code to that particular worksheet, a box
came up telling me that there were macros. I don't have any macros in the
workbook and don't understand why this box comes up.

Hope I explained this correctly.

Thanks

 

[Gord Dibben]

Any VBA code is treated by Excel warning as a "macro".

If you have worksheet code like the SheetActivate code I posted, that will
raise the warning.

If you have an empty genral module, that will raise a warning also even
there is no code.


Gord