message box:when the work book opens?

I have been given a code to make a msg box pop up on opening the work book,
but is there any way to have that msg box pop up on a certain sheet insted of
it poping up when the work book opens?

thanks for any help given.
regards
Stuart

Stuart

You could use the Sheet_Activate() event, like so

Private Sub Worksheet_Activate()
MsgBox "Activated " & Me.Name
End Sub

To implement, check here:

http://www.nickhodge.co.uk/vba/vbaim....htm#EventCode

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"swec74" <swec74@discussions.microsoft.com> wrote in message
news:9B6655FB-2EF2-4B67-B093-044B366FF4BD@microsoft.com...
>I have been given a code to make a msg box pop up on opening the work book,
> but is there any way to have that msg box pop up on a certain sheet insted
> of
> it poping up when the work book opens?
>
> thanks for any help given.
> regards
> Stuart

right click sheet tab>view code>copy this>SAVE

Private Sub Worksheet_Activate()
MsgBox "I popped up"
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"swec74" <swec74@discussions.microsoft.com> wrote in message
news:9B6655FB-2EF2-4B67-B093-044B366FF4BD@microsoft.com...
> I have been given a code to make a msg box pop up on opening the work
book,
> but is there any way to have that msg box pop up on a certain sheet insted
of
> it poping up when the work book opens?
>
> thanks for any help given.
> regards
> Stuart

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

Paste these lines in that module.

Private Sub Worksheet_Activate()
''your pop-up message code goes here
End Sub

Delete the code from the module it presently resides in.

Gord Dibben Excel MVP


On Sun, 2 Jan 2005 02:01:01 -0800, swec74 <swec74@discussions.microsoft.com>
wrote:

>I have been given a code to make a msg box pop up on opening the work book,
>but is there any way to have that msg box pop up on a certain sheet insted of
>it poping up when the work book opens?
>
>thanks for any help given.
>regards
> Stuart

thank u it helpped alot but from that code is there any way to have it so
the message pops up for the first time i open the work book instead of every
time i click on the tab? thanks agian for ur help.

"Gord Dibben" wrote:

> Right-click on the sheet tab and "View Code"
>
> Paste these lines in that module.
>
> Private Sub Worksheet_Activate()
> ''your pop-up message code goes here
> End Sub
>
> Delete the code from the module it presently resides in.
>
> Gord Dibben Excel MVP
>
>
> On Sun, 2 Jan 2005 02:01:01 -0800, swec74 <swec74@discussions.microsoft.com>
> wrote:
>
> >I have been given a code to make a msg box pop up on opening the work book,
> >but is there any way to have that msg box pop up on a certain sheet insted of
> >it poping up when the work book opens?
> >
> >thanks for any help given.
> >regards
> > Stuart
>
>

There are probably more elegant methods, but........

Copy/paste this to the worksheet.

Private Sub Worksheet_Activate()
If Range("A1").Value = "phnorton" Then Exit Sub
MsgBox "hoohah"
Range("A1").Value = "phnorton"
End Sub


Now copy/paste this to the ThisWorkbook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Range("A1").ClearContents
End Sub


Adjust range, sheetname and value to suit.


Gord

On Sat, 8 Jan 2005 03:27:01 -0800, swec74 <swec74@discussions.microsoft.com>
wrote:

>thank u it helpped alot but from that code is there any way to have it so
>the message pops up for the first time i open the work book instead of every
>time i click on the tab? thanks agian for ur help.
>
>"Gord Dibben" wrote:
>
>> Right-click on the sheet tab and "View Code"
>>
>> Paste these lines in that module.
>>
>> Private Sub Worksheet_Activate()
>> ''your pop-up message code goes here
>> End Sub
>>
>> Delete the code from the module it presently resides in.
>>
>> Gord Dibben Excel MVP
>>
>>
>> On Sun, 2 Jan 2005 02:01:01 -0800, swec74 <swec74@discussions.microsoft.com>
>> wrote:
>>
>> >I have been given a code to make a msg box pop up on opening the work book,
>> >but is there any way to have that msg box pop up on a certain sheet insted of
>> >it poping up when the work book opens?
>> >
>> >thanks for any help given.
>> >regards
>> > Stuart
>>
>>