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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top