Hello,

Is there a way to detect if a user has created a new worksheet in a workbook?

Whenever a user has created a new sheet, I need to populate some cells on
the Master sheet (1st tab) plus reference the newly added worksheet from the
user.

Any suggesstions would be greatly appreciated. Thanks!

Yes! Use a Workbook_NewSheet event. In the VBE, go to your project and find
the ThisWorkbook module in the Microsoft Excel Objects folder. Then enter:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
'your code here using 'Sh' to reference the new sheet
End Sub

HTH,
Matthew Pfluger

"sharonm" wrote:

> Hello,
>
> Is there a way to detect if a user has created a new worksheet in a workbook?
>
> Whenever a user has created a new sheet, I need to populate some cells on
> the Master sheet (1st tab) plus reference the newly added worksheet from the
> user.
>
> Any suggesstions would be greatly appreciated. Thanks!
>

hi
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sheets("sheet1").Range("B2").Value = "PSST!"
Sheets("sheet1").Range("B3").FormulaR1C1 = _
"Someone added a sheet."
Sheets("Sheet1").Range("B4").Value = _
Application.UserName 'pc name
' eviron("username")= network name
Sheets("sheet1").Range("B5").Value = Now()
Sheets("sheet1").Range("B6").Value = "Have a nice day. :-)"
End Sub

this is thisworkbook code.

regards
FSt1

"sharonm" wrote:

> Hello,
>
> Is there a way to detect if a user has created a new worksheet in a workbook?
>
> Whenever a user has created a new sheet, I need to populate some cells on
> the Master sheet (1st tab) plus reference the newly added worksheet from the
> user.
>
> Any suggesstions would be greatly appreciated. Thanks!
>

Thanks to both of you!

Your suggesstions are great to detect when a user adds a sheet!

But, can this be modified some way to detect if a user copies an existing
sheet by Right Clicking on the name of the sheet, Selecting the sheet, and
check the Create Copy box.

Thanks!

"FSt1" wrote:

> hi
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Sheets("sheet1").Range("B2").Value = "PSST!"
> Sheets("sheet1").Range("B3").FormulaR1C1 = _
> "Someone added a sheet."
> Sheets("Sheet1").Range("B4").Value = _
> Application.UserName 'pc name
> ' eviron("username")= network name
> Sheets("sheet1").Range("B5").Value = Now()
> Sheets("sheet1").Range("B6").Value = "Have a nice day. :-)"
> End Sub
>
> this is thisworkbook code.
>
> regards
> FSt1
>
> "sharonm" wrote:
>
> > Hello,
> >
> > Is there a way to detect if a user has created a new worksheet in a workbook?
> >
> > Whenever a user has created a new sheet, I need to populate some cells on
> > the Master sheet (1st tab) plus reference the newly added worksheet from the
> > user.
> >
> > Any suggesstions would be greatly appreciated. Thanks!
> >