MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Adding a Worksheet_Event when adding a worksheet via VBA

  Asked By: Misty    Date: Mar 21    Category: MS Office    Views: 3741

Is there a way to add a worksheet change event programatically when
adding a worksheet? Basically, I want to add a new worksheet and then
create VBA to run against that worksheet every time a cell in the new
worksheet changes without having to manually create the worksheet
change event.



4 Answers Found

Answer #1    Answered By: Damian Jones     Answered On: Mar 21

An easy way is to already have a worksheet  (perhaps
hidden) with the event  code you want. Copy that sheet
instead of adding  a new worksheet, and the
Worksheet_Change event code will be copied along with
the sheet. If you need to, copy any sheet witht eh
event logic you need, then select and clear/delete all
the cells on the sheet.

You also might find some help at the Erlandsen Data
Consulting site (lots of great vba  stuff there):


Answer #2    Answered By: Eamon Jones     Answered On: Mar 21

Another thing to look at. If you want to do the same "cell change" action
on every cell  in the entire workbook, then look at the Workbook_SheetChange
event in the ThisWorkbook module. This fires on any change  to any cell.
(Its name is not terribly clear - it has nothing to do with changing from
one worksheet  to another.)

Answer #3    Answered By: Rachael Ferguson     Answered On: Mar 21

Place this code in the ThisWorkbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim NewSheet As Worksheet
Set NewSheet = Sheets(ActiveSheet.Name)
Code = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
Code = Code & "MsgBox ""your code here""" & vbCrLf
Code = Code & "End Sub"
With ThisWorkbook.VBProject.VBComponents(NewSheet.Name).CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code
End With
End Sub

On addition of a new sheet to the workbook it places these three lines
of code in the new sheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "your code here"
End Sub

Answer #4    Answered By: Muhammad Evans     Answered On: Mar 21

not sure if you got an answer to this yet...quick off the top of my head i
would suggest a workbook on change  event and then test the worksheet  name
against the new worksheet.. if it's not the new one.. then do nothing

Didn't find what you were looking for? Find more on Adding a Worksheet_Event when adding a worksheet via VBA Or get search suggestion and latest updates.