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

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
636
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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
636
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,968
Members
430,098
Latest member
bemmelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top