Run Macro in .xlsm file on cell change in .xlsx file

[Tim Hanson]

I have all my Macros in a .xlsm workbook and they act on a 2nd open .xlsx worxbook

I need to run a macro on cell change within a range (cells in column A) in the .xlsx file on sheet(2)

Is it possible to do this from the 1st .xlsm workbook?

I found many variations of the macro below but they all need the code to be placed in the Sheet of the column with the cells that change, which is a .xlsx file not a .xlsm file

Thanks for any help on this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."
     
    End If
End Sub

 

[NARAYANK991]

Hi Tim ,

First , can you explain why you want this done ?

Second , can you upload both files , and detail which cells in the .xlsm file depend on the cell(s) in the .xlsx file that needs to be monitored ?

Narayan

 

[Tim Hanson]

Hello Narayn, I decided to use a command button tied to a procedure, it seemed easier. Thank you for you inquiry.

 

[SirJB7]

Hi, Tim Hanson!

Regarding your original question it's not possible to track worksheet change events of a .xlsx workbook since that code must be placed in the worksheet class module of that WS, hence converting the file into a .xlsm.

Regards!

 

[Tim Hanson]

Thanks SirJB7