Start a macro everytime a cell in a group of sheets is changed

[espenskeie]

Hi folks

I try to put together a code that will start a macro whenever a cell is modified in a group of sheets from sheets("X1") to sheets("X40"). I've started out with this, but is seems to be all wrong:

Private Sub Worksheet_Change(ByVal target As Range)
Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array("X1", "X2", "X3", "X4", "X5"))


Dim target As Range
Dim sheetObject As Worksheet
If Not Intersect(target, Range.sheetsArray) Is Nothing Then WriteLog
End Sub

Anyone that know how to solve this?

Kind regards
Espen

 

[VoG]

Try in the ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsNumeric(Application.Match(Sh.Name, Array("X1", "X2", "X3", "X4", "X5"), 0)) Then Call WriteLog
End Sub

 

[espenskeie]

Thanks VoG, but i cannot find the module, this workbook was originally created as an xlsx file, I have used "SaveAs" and saved it as an macro enabled workbook, but i cannot find the ThisWorkbook module. Can I somehow insert it?


Never mind, it was ofcourse at the bottom of the list of all the 80 sheets.....

 

[VoG]

Press ALT + F11. In the Project window you should see ThisWorkbook. Double click it and paste in the code.

 

[espenskeie]

VoG, code works excellent

Thanks!