Write macro that references another sheet?

Shack

New Member
Joined
Oct 26, 2005
Messages
6
[This post was split from this thread]

NateO said:
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
Shack said:
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?
 
Upvote 0
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.'
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,217,682
Messages
6,137,961
Members
450,101
Latest member
Mrupe86

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