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

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
632
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try in the ThisWorkbook module

Code:
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

Well-known Member
Joined
Mar 30, 2009
Messages
632
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..... :eek:
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Press ALT + F11. In the Project window you should see ThisWorkbook. Double click it and paste in the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top