0

I am trying to find a way to use the "Workbook_SheetChange" where the code is written in the ThisWorkbook code or in a separate module but works on a another sheet. The sheets are created by another macro and data is uploaded and processed separately, i don't know the number of sheets that will be create. Is there a way i can use "Workbook_SheetChange" : - when the code is written in a separate module - or to copy the code automatically when the new sheet is created.

Community
  • 1
  • 1
Andrei Neagu
  • 1
  • 2
  • 2
    Possible duplicate of http://stackoverflow.com/questions/15082722/adding-code-to-new-excel-sheet-dynamically – Techie May 16 '16 at 13:26
  • Not sure what you're after - sounds like a Change event that will work on all sheets? Just add your code to the `Workbook_SheetChange` event in the Thisworkbook module and it will doing what it sounds like you're asking. e.g. Add this code to the ThisWorkbook module: `Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range): MsgBox Sh.Name & " " & Target.Address: End Sub` - whenever you change a value on _any_ sheet it will tell you the sheet name and the cell address. – Darren Bartrup-Cook May 16 '16 at 13:37

1 Answers1

0

I agree this is a duplicate of Adding code to new excel sheet dynamically, but it might still require some explanation.

In VBA, the event Workbook_SheetChange will fire on every worksheet change. So if you want to run 2 different procedures you can do something like:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _ 
 ByVal Source As Range) 
 If ActiveSheet.Name = "Sheet1" then
     'Do one thing
 Else
     'Do another
 End If
End Sub

This is much better than "writing code that writes code."

Now if you're creating a new workbook it gets harder - let us know if that's what you need.

Community
  • 1
  • 1
Charlie
  • 2,004
  • 6
  • 20
  • 40
  • @MacroMan ... you are absolutely correct, and I am absolutely wrong! What I said is true about _Access_ but not Excel. Editing so that no one listens to my incorrect advice! – C. White May 16 '16 at 14:34
  • Thank you for all the responses but i have tried this, if the code is in the specific sheet code with "Worksheet_" it works perfectly but when it is in the workbook code it doesn't. i some times get an error or it does nothing. Run-time Error -2147417848 (80010108) Method 'Cells' of object '_worksheet' failed – Andrei Neagu May 16 '16 at 15:16
  • I the end all works best. I found that double clicking works beater for me : [Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)] – Andrei Neagu May 16 '16 at 15:25