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
Which calls
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
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
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.
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
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
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
Code:
Set mySheet = ActiveWorkbook.Worksheets("SupplierSelectionGrading")
I am hoping someone can point me in the right direction, and that I have been clear enough in my problem.