Run module ON OPEN event??

[Guest]

I'm trying to create a subroutine that runs as soon as anyone opens my network spreadsheet. I can get it to work if I run it as a macro, but I cannot get it to run ON OPEN. Here's my code:

Private Sub APP_Workbook_Open(ByVal WB As Workbook)
USER = Application.UserName
USER = UCase(USER)
Workbooks("xxxx.xls").HasRoutingSlip = True
With Workbooks("xxxx.xls").RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = "MG"
.Subject = "SPREADSHEET BEING ALTERED"
.Message = USER & "<-- " & "Has Altered The Attached Workbook"
End With
Workbooks("xxxx.xls").Route
End Sub

I've changed the name of the workbook to xxxx for this message.
Any help would be greatly appreciated.

Thanks,

Mike G.

 

[Guest]

Hi Mike,

Why not put the code into the "Workbooks" section and use the Workbook_Open event?

-------------------------------------------------

Private Sub Workbook_Open()

' Paste your code here, replacing "Workbooks("xxxx.xls")." with "ThisWorkbook"

End Sub

-------------------------------------------------

SuperJas.




----- MG wrote: -----

I'm trying to create a subroutine that runs as soon as anyone opens my network spreadsheet. I can get it to work if I run it as a macro, but I cannot get it to run ON OPEN. Here's my code:

Private Sub APP_Workbook_Open(ByVal WB As Workbook)
USER = Application.UserName
USER = UCase(USER)
Workbooks("xxxx.xls").HasRoutingSlip = True
With Workbooks("xxxx.xls").RoutingSlip
.Delivery = xlOneAfterAnother
.Recipients = "MG"
.Subject = "SPREADSHEET BEING ALTERED"
.Message = USER & "<-- " & "Has Altered The Attached Workbook"
End With
Workbooks("xxxx.xls").Route
End Sub

I've changed the name of the workbook to xxxx for this message.
Any help would be greatly appreciated.

Thanks,

Mike G.