Thread: Is there an "OnOpen" event for Excel

Hi,
I wish to display a message box when a user opens the spreadsheet. How do I
make my message box code run? I have tried the following but it does not seem
to work:

Sub DisplayMessageBox()
If Sheet("Price Change Request").active = True Then
MsgBox "hello"
End if
End Sub

How do I trigger this sub routine or is there an "OnOpen" event that I can
attach my code to?

Graeme.

If you name the macro Auto_Open, it will execute whenever Excel
is opened by the user (it will not run if the workbook is opened
via VBA). If you use the Workbook_Open macro in the ThisWorkbook
module, that procedure will run whenever the file opens, either
by the user or by VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jurrasicway" <Jurrasicway@discussions.microsoft.com> wrote in
message
news:A4A7F0C1-E0DA-4D5B-83FA-9C36E4BA070D@microsoft.com...
> Hi,
> I wish to display a message box when a user opens the
> spreadsheet. How do I
> make my message box code run? I have tried the following but it
> does not seem
> to work:
>
> Sub DisplayMessageBox()
> If Sheet("Price Change Request").active = True Then
> MsgBox "hello"
> End if
> End Sub
>
> How do I trigger this sub routine or is there an "OnOpen" event
> that I can
> attach my code to?
>
> Graeme.
>

Yep, use Workbook_Open. In the VBE Project Browser, find your project, open
the folder, "Microsoft Excel Objects" and double-click on the "ThisWorkbook"
item. Select "Workbook" from the "(General)" drop list, and select "Open"
from the "(Declarations)" drop list. Put your code in the Workbook_Open sub.

"Jurrasicway" wrote:

> Hi,
> I wish to display a message box when a user opens the spreadsheet. How do I
> make my message box code run? I have tried the following but it does not seem
> to work:
>
> Sub DisplayMessageBox()
> If Sheet("Price Change Request").active = True Then
> MsgBox "hello"
> End if
> End Sub
>
> How do I trigger this sub routine or is there an "OnOpen" event that I can
> attach my code to?
>
> Graeme.
>

Hi

There is an Open event for Excel workbook. Open VBA editor, and in VBA
Project window, right-click on ThisWorkbook object, and select View Code.
Then select Workbook in left dropdown at top of code window - Open event is
the one automatically created after that.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"Jurrasicway" <Jurrasicway@discussions.microsoft.com> wrote in message
news:A4A7F0C1-E0DA-4D5B-83FA-9C36E4BA070D@microsoft.com...
> Hi,
> I wish to display a message box when a user opens the spreadsheet. How do
> I
> make my message box code run? I have tried the following but it does not
> seem
> to work:
>
> Sub DisplayMessageBox()
> If Sheet("Price Change Request").active = True Then
> MsgBox "hello"
> End if
> End Sub
>
> How do I trigger this sub routine or is there an "OnOpen" event that I can
> attach my code to?
>
> Graeme.
>

In the VB editor double-click the "ThisWorkbook" on the properties
window, then in the code window select workbook in the left combobox
and open in the right combobox. put your MsgBox code in there.

HTH

Die_Another_Day

Jurrasicway wrote:
> Hi,
> I wish to display a message box when a user opens the spreadsheet. How do I
> make my message box code run? I have tried the following but it does not seem
> to work:
>
> Sub DisplayMessageBox()
> If Sheet("Price Change Request").active = True Then
> MsgBox "hello"
> End if
> End Sub
>
> How do I trigger this sub routine or is there an "OnOpen" event that I can
> attach my code to?
>
> Graeme.

If you want to display the message when a sheet is activated, put your
code in the Sheet Object Activate sub. i.e.

Private Sub Worksheet_Activate() ' Built in subroutine
MsgBox "hello"
End Sub

SteveM

Jurrasicway wrote:
> Hi,
> I wish to display a message box when a user opens the spreadsheet. How do I
> make my message box code run? I have tried the following but it does not seem
> to work:
>
> Sub DisplayMessageBox()
> If Sheet("Price Change Request").active = True Then
> MsgBox "hello"
> End if
> End Sub
>
> How do I trigger this sub routine or is there an "OnOpen" event that I can
> attach my code to?
>
> Graeme.