Execute macro on change of cell (cell value is determined remotely)

n35

New Member
Joined
Nov 19, 2010
Messages
24
Hi,

In short
Two workbooks, macro in one workbook, needs to be triggered/called/executed when a value in the other workbook changes.

Been all over Google, and also this forum. So if there is an answer to this question, I haven't been able to find it, or I don't know how to extract the answer from a similar question. So here goes.

I use Windows 7x64, and MS-Excel 2007.



I've created a macro that sorts some cells, this is easy enough. Calling this macro when I manually change a cell in the worksheet, is also easy enough.

However i am unable to get it to run when the change occurs from a formula. For instance:

I have two workbooks.
Control.xlsm
SupplierSelection.xlsm

Control.xlsm
Has a cell where I change the value manually, where when changed, I need to trigger some macros in SupplierSelection.xlsm, more specifically I need to trigger a sorting macro.
So in SupplierSelection.xlsm, I have a cell that references a cell in Control.xlsm (A1 for instance), and when this cell(control.xlsm) changes value, either by manual change or by calculation, the sorting macro 'sortOwn' should be triggerd in SupplierSelection.xlsm.

The sorting macro I've got working, but can post it if it is of interest.

I've tried a number of solutions, these solutions are implemenations of solutions I've found elsewhere, I can't give proper credit unfortunately as I've long since lost the page where I found them.

1. auto_open
Code:
Sub auto_open()

   ' Run the macro DidCellsChange any time a entry is made in a
   ' cell in Sheet1.
   ThisWorkbook.Worksheets("Sheet1").OnEntry = "cellChange"
End Sub
Which calls
Code:
Sub cellChange()
  Dim KeyCells As String
   ' Define which cells should trigger the KeyCellsChanged macro.
   KeyCells = "A11"

   ' If the Activecell is one of the key cells, call the
   ' KeyCellsChanged macro.
   If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
   Is Nothing Then sortOwn

End Sub
2. Worksheet_change
This solution is not worth posting as far as I know, simply because it only works when the user manually alters or refreshes the workbook.

3. Worksheet_calculate
Code:
Private Sub Worksheet_Calculate()

 Set mySheet = ActiveWorkbook.Worksheets("SupplierSelectionGrading")
   Set myRange = mySheet.Range("B30").CurrentRegion

If Sheets(mySheet).Range(myRange).Value < 0 Then
    Call sortOwn

MsgBox "sortOwn called" ' for paying attention only :)
End If

End Sub
This last one here, gives an runtime error '9' subscript out of range. When I click on Debud, it highlights the following line from Worksheet_calculate
Code:
Set mySheet = ActiveWorkbook.Worksheets("SupplierSelectionGrading")
Granted I'm not sure if it is because It doesnt work, when the acticeworkbook is actually Control.xlsm, but I havent been able to figure out how to make this work.

I am hoping someone can point me in the right direction, and that I have been clear enough in my problem.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Is the worksheet_calculate event handler triggering when you want it to?

The subscript error means it can't find such a worksheet in the active workbook - is it named exactly correctly? leading spaces and such like. If so, consider a global variable assigned to that workbook elsewhere in the code, for example, if the code opens that file, then that would be a good time to assign it to a global variable.
 

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
This on the Control book sheet works for me.
Of course I may misunderstand your need.
And I don't know what you've tried w the Worksheet_Change event.
This fired 'sortOwn' whether changed manual or w/ VBA
from another book.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.Run "SupplierSelection.xlsm!sortOwn"
    End If
End Sub
HTH, Roy
 

n35

New Member
Joined
Nov 19, 2010
Messages
24
p45cal

Thank you for your reply, as far as i can see, all things are named correctly, but Im assuming it was something to do with wanting to trigger a macro in the active workbook, and the macro was not in the active macro, but in a different macro. If you have other suggestions though, I am open for them :)


Warship/Roy
This on the Control book sheet works for me.
Of course I may misunderstand your need.
And I don't know what you've tried w the Worksheet_Change event.
This fired 'sortOwn' whether changed manual or w/ VBA
from another book.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.Run "SupplierSelection.xlsm!sortOwn"
    End If
End Sub
HTH, Roy
I've started completly over, but regarding the worksheet_change, I just tried triggering the macro using worksheet_change, didnt work for me.

Your suggestions gives me an error.
Code:
  Application.Run "SupplierSelection.xlsm!sortOwn"
Run-time error '1004'
Code:
Cannot run th macro 'SupllierSelection.xlsm!sortOwn'. The macro may not be available in this workbook or all macros may be disabled.
I places the sortOwn macro in both workbooks just be on the safe side, I've altered the names so be consistent with what it filenames are, but so far no change.

Im not too sure on how you got it working, could you elaborate please?:confused:
 

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
There's not a whole lot to eleborate:
I created two workbooks: SupplierSelection.xlsm & Control.xlsm

On Sheet1 of Control.xlsm I put
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.Run "SupplierSelection.xlsm!sortOwn"
    End If
End Sub
With both books open and with macros enabled, when I change
Workbooks("Control.xlsm").Sheets(1).Range("A1") by any means,
sortOwn fires.

Your run-time error indicates either macros are NOT enabled
or
You are trying to run a macro that does not exist - so a type'o

You do recognise that the Application.Run line contains the
exact names of both your workbook and your macro, you mention
altering the names
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
If A1 contains a formula, this will cause MyRoutine to run when A1 is re-calculated. This is limited to formulas that refer to cells on the same sheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRange As Range
    Set keyRange = Range("A1")
    On Error Resume Next
    Set keyRange = Application.Intersect(Target, keyRange.Precedents)
    On Error GoTo 0
    If Not keyRange Is Nothing Then
        Call MyRoutine
    End If
End Sub
 

n35

New Member
Joined
Nov 19, 2010
Messages
24
There's not a whole lot to eleborate:
I created two workbooks: SupplierSelection.xlsm & Control.xlsm

On Sheet1 of Control.xlsm I put
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Application.Run "SupplierSelection.xlsm!sortOwn"
    End If
End Sub
With both books open and with macros enabled, when I change
Workbooks("Control.xlsm").Sheets(1).Range("A1") by any means,
sortOwn fires.

Your run-time error indicates either macros are NOT enabled
or
You are trying to run a macro that does not exist - so a type'o

You do recognise that the Application.Run line contains the
exact names of both your workbook and your macro, you mention
altering the names
Thank you for your help so far. I must be too dense to get this working.

Basically, what I did was start with two empty workbooks and enter the code you supplied into control.xlsm, and the sort code below
Code:
Sub sortOwn()
  Dim mySheet As Worksheet
   Dim myRange As Range
   
   Set mySheet = ActiveWorkbook.Worksheets("Sheet1")
   Set myRange = mySheet.Range("A1").CurrentRegion
   
   mySheet.Activate
   myRange.Sort Key1:=Range("c2"), Order1:=xlDescending, Header:=xlGuess
   myRange.Cells(1, 1).Select
End Sub
The sort code i've put into sheet1 in SupplierSelection.xlsm, where i've just entered some numbers in the first 3 columns, 'A,B,C' and the sortOwn macro is then suposed to sort the numbers using colum C as a reference, with the highest number first.

When I do this, I get the following error
Code:
Run-time error '1004'
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isnt the same or blank.
Now, I am guessing, the problem here lies within my sortOwn code. I think because I use activeworkbook, it is trying to call it in the workbook I am making the change from, namely - control.xlsm.

So I guess my first post was incorrect, I could not quite get the sorting macro right.

So, to follow up. How do I change the sortOwn macro to work in the correct workbook, or just the workbook where it is entered in a module?

Thank you so much for your help so far.

mikerickson
Thank you for your suggestion, however I need a solution that works across workbooks, as I am hoping to automate some functionality based on what happens in the control sheet.
 

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
You're getting close, give yourself a break!
Code:
Sub sortOwn()
  Dim mySheet As Worksheet
   Dim myRange As Range
   
   Set mySheet = Workbooks("SupplierSelection.xlsm").Worksheets("Sheet1")
   Set myRange = mySheet.Range("A1").CurrentRegion
   
   mySheet.Activate
   myRange.Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlGuess
   myRange.Cells(1, 1).Select
End Sub
 

n35

New Member
Joined
Nov 19, 2010
Messages
24
You're getting close, give yourself a break!
Code:
Sub sortOwn()
  Dim mySheet As Worksheet
   Dim myRange As Range
   
   Set mySheet = Workbooks("SupplierSelection.xlsm").Worksheets("Sheet1")
   Set myRange = mySheet.Range("A1").CurrentRegion
   
   mySheet.Activate
   myRange.Sort Key1:=Range("C:C"), Order1:=xlDescending, Header:=xlGuess
   myRange.Cells(1, 1).Select
End Sub
Brilliant, thank you very much for you help. It works just as intended, and I've been able to use it as a base for all the other methods I've had to implement. Brilliant!.

Ill consider this as Solved.

How do i mark it as solved?
 
Last edited:

Forum statistics

Threads
1,081,793
Messages
5,361,316
Members
400,625
Latest member
Asraful Alam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top