Pop Up Message when User Copies or Creates New Sheet

[christopher.pratt]

I need a macro that will create a popup message any time 1) a new worksheet is created and 2) a worksheet is copied. The contents of the message left aside (use "message" as our example message) could someone help me with a simple code for this?

 

[venkat1926]

right click the excel icon to the left of "file" in menu bar and click "view code"
in the window that comes up type or copy paste this event macro

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "new sheet added"
End Sub

 

[christopher.pratt]

Under "View Code" , does the code go under an excel object, this workbook, a userform, a module? Appreciate the help.

 

[venkat1926]

If it goes under view code the codes operate once the event in the beginning happens
for example when your insert a new sheet msgbox comes up.. try this test.
open a blankworkbook and copythe code as instructed in my mesage.
now go to sheet and insert a sheet and see what happens.

 

[christopher.pratt]

I am working with Microsoft Office 2007, so to begin with the steps taken to get to "View Code" were a little different then you initially outlined. I did find it though, opened "View Code" which automatically takes me to a sheet where I enter the code as directed....upon closing VBA and creating a new sheet nothing out of the ordinary happens. So no message box unfortunatley. User error, or 2007 problems.

 

[venkat1926]

the event code works in my excel 2002 so it must work in excel 2007. When I checked up with google search there is such an event as this in excel 2007
Private Sub Workbook_NewSheet(ByVal Sh As Object)

you have to park the code I have give in WORKBOOK EVENT and NOT in worksheet event.
Another way of opening WORKBOOK EVENT Class MODULE is like this
open vba editor
hit contorl + R
you will see all the ope nwork book names . In your particular workbook if the sign to the left of your workbook name is negative(if this is not negative click the positive sign ) you will get the list of the sheets and also "thisworkbook" . click "thisworkbook".
a window will open on the left arrow click and choose "workbook'
automatically a default event will open ignore this
below that type the code i have given .
now you try to insert a worksheet

also make prfofues use of HELP under excel 2007vba

 

[christopher.pratt]

Yes, it works now....the step I was missing from the first time you gave me the code was flipping from "General" to "Workbook" in the window. Thank you

 

[christopher.pratt]

I've noticed that the code works only more making new sheets...Can we adjust it to work if you copy sheets as well?

 

[venkat1926]

yes that event code works only when a new sheet is insertd
for copying sheet we have to cheat the excel somewhat
this is also a thisworkbook event code
in the same manner as you copied the previous event code this code is also to be copied
try and confirm whether it works
when you rightclick a tab and clcik move or copy and check "create a copy" then the event code is automatically fired and you get the message box. try in a blank file

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim j As Integer, name As String
'MsgBox ActiveSheet.Name
On Error Resume Next
j = WorksheetFunction.Search("(", ActiveSheet.name)
'MsgBox j
If j = 0 Then Exit Sub
name = Left(ActiveSheet.name, j - 2)
'MsgBox name
 MsgBox name & " " & "copied"
End Sub

 

[christopher.pratt]

It worked...although it only worked for the move/copy feature and not insert new worksheet...so I just put the original and new codes both in thisworkbook and all was well...Thank you