Write macro that references another sheet?

[Shack]

[This post was split from this thread]

Hello Peter, change the target.address = "$c$2" to:

If Not Intersect(Target, [a1,c2,d3]) Is Nothing Then

Where a1 and d3 are precedents causing c2's calculated value to change.

I realize this thread has been dead for a couple of years, but I'm hoping you guys are still actively checking replies.....

This is my first macro, and I've followed all the responses in this thread so far, but how do you write a macro that references another worksheet?

my macro:

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo TheEnd
If Not Intersect(Target, [a2,c4,a3]) Is Nothing Then
MsgBox "I'm running"
Application.EnableEvents = False
Range("C7").GoalSeek Goal:=0.0001, ChangingCell:=Range("C16")
End If
TheEnd:
Application.EnableEvents = True
End Sub

What if the precedents (a2 and a3) are in another worksheet, within the same Excel file?


Thanks in advance,
Shack

Edited by Von Pookie

 

[just_jon]

Cannot happen.

A worksheet change macro is an event macro, not run explicitly by you but firing on each instance of the event, in this case a change to the worksheet.

So, in this line

If Not Intersect(Target, [a2,c4,a3]) Is Nothing Then

it would not be possible for the Target reference to be either A2 or C3 as they would not reside on the instant sheet.

What I'm guessing you need to another event macro on the other sheet, but but don't have enough info.

 

[Shack]

Thanks for the quick reply. It looks as though I'll just manipulate sheet1 and write the event macro there, with cell references in sheet2.

Thanks again

 

[Tazguy37]

What about using a Workbook_SheetChange() event instead?

 

[Shack]

What about using a Workbook_SheetChange() event instead?

Sure, I'll try it. How do I write that in the macro?

 

[just_jon]

Not sure, but what you may be looking for might be

<font face=Courier New><SPAN style="color:#007F00">' put this in the sheet module where c4, c7, and c16 reside</SPAN>
<SPAN style="color:#007F00">' assume for now this sheet's name is Sheet1</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [c4]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
MsgBox "I'm running"
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Range("C7").GoalSeek Goal:=0.0001, ChangingCell:=Range("C16")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
TheEnd:
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">' then put this in the sheet module where a2 and a3 reside</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [c4]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
MsgBox "I'm running"
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> Sheet1
    .Range("C7").GoalSeek Goal:=0.0001, ChangingCell:=.Range("C16")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
TheEnd:
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Guessing, though...

 

[Shack]

Thanks for the reply just_jon. Still no luck though. I don't think I'm giving you enough from my description though.

So let me be a little better about that:
I have 2 sheets within the same Excel file. One sheet, called 'title page' has a series of user input cells. That cell is then referenced by the second sheet, called 'calc,' which then runs a series of calculations, based on the user input. Unfortunately, one of the calculations requires a goal seek operation.

What I'm trying to accomplish:
I'd like the macro in the sheet 'calc' to run as soon as the user inputs any number within the input cell on 'title page.' I can get the macro to run appropriately, but only when I make a change within the sheet where the macro is embedded. I simply want any one of the the input cells to act like a trigger for the goal seek macro on the other sheet.

Sorry for the vague description prior, hope this helps a little more.

 

[just_jon]

Thanks for the reply just_jon. Still no luck though. I don't think I'm giving you enough from my description though.

So let me be a little better about that:
I have 2 sheets within the same Excel file. One sheet, called 'title page' has a series of user input cells. That cell is then referenced by the second sheet, called 'calc,' which then runs a series of calculations, based on the user input. Unfortunately, one of the calculations requires a goal seek operation.

What I'm trying to accomplish:
I'd like the macro in the sheet 'calc' to run as soon as the user inputs any number within the input cell on 'title page.' I can get the macro to run appropriately, but only when I make a change within the sheet where the macro is embedded. I simply want any one of the the input cells to act like a trigger for the goal seek macro on the other sheet.

Sorry for the vague description prior, hope this helps a little more.

OK; What cell{s} on 'title page' should trigger the calc on 'calc', and is this calc driven by its own macro?

 

[Shack]

The cells on 'title page' that should trigger the macro (in the sheet 'calc') are B4, C5, and D6.

The input for these cells is then (cell) referenced by the 'calc' sheet, and used to make calculations. I have not written another macro for these automatic calculations. They are simply calculated as the cells that they reference change.
The calculations then produce values that are part of the goal seek equation. The cell that is the 'goal' in the goal seek application is C7 on sheet 'calc,' having a value of 0.0001. The cell that varies with goal seek is C16 on sheet 'calc.'

 

[just_jon]

Maybe

1] Use an event macro on the 1st sheet

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Excel.Range)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [a1]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "I'm running"
    Application.Run ("Book2.xls!MySub")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
TheEnd:
End <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

that would

2] run another macro stored in standard module [ Module1, etc ] which would actually run your goalseek.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MySub()
<SPAN style="color:#00007F">With</SPAN> Sheet2
MsgBox "running on Sheet2"
<SPAN style="color:#007F00">' enter goalseek cmd here, prefacing ranges with a period</SPAN>
<SPAN style="color:#007F00">' as:   .Range("C16")  etc, to tajke advantage of the With stmt</SPAN>
<SPAN style="color:#007F00">' ex.: .Range("C7").GoalSeek Goal:=0.0001, ChangingCell:=.Range("C16")</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>



</FONT>

Might need an on-open macro - would go in the ThisWorkbook module - to make sure the book is set to calculation=auto...

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
Application.Calculation = xlCalculationAutomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>