execute code in one worksheet when change occurs in another

[suzetter]

I have two workbooks open. Let's call them WkBookA and WkBookB. Cell B1
in WkBookB is linked to cell A1 in WkBookA. Hence when I change the
text in A1 in WkBookA, the text in B1 in WkBookB chnages. I want, that
when the user changes the text in A1 in WkBookA, and the text
subsequently changes in B1 in WkBookB, that some code is triggered. I
have the code that works, but it doesn't work unless the user changes
the information directly in WkBookB. In other words WkBookA is the
focus obviously when you are changing the text in A1, so WkBookB
doesn't have the focus and so the following code doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myexcel As Object
Dim myworkbook As Object
Dim myworksheet As Object
Dim wkbook As Workbook

Set myexcel = GetObject(, "Excel.Application") 'Point to active excel
application

If Target.Address = "$B$1" Then
Set myworkbook = Excel.Application.Workbooks("IRReports.xls")
Set myworksheet = myworkbook.Worksheets("PIR-DT DAY")
'Here is where the code would go to do what I want to do
myworksheet.Range("C1").Value = "Changed"
End If
End Sub

But like I said when I'm changing the text in A1 in WkBookA and the
focus is on WkBookA, the Private Sub Worksheet_Change(ByVal Target As
Range) in WkBookB isn't triggered

 

[Krem]

You should be able to use the same code in WkBookA and use 'I
Target.Address = "$A$1" Then' instead

 

[suzetter]

I know I can have the same code in WkBookA and I've tried it and i
works perfectly. But I was trying not to have the code in WkBook
because everytime I change the text in A1, I'll have to check i
WkBookB is open. I know this doesn't sound like a lot of work, bu
WkBookB is one of many workbooks that have to be triggered. Thanks fo
your input anywa

 

[BAC]

Move the Change code to WkBookA and change the Target to $A$1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myexcel As Object
Dim myworkbook As Object
Dim myworksheet As Object
Dim wkbook As Workbook

Set myexcel = GetObject(, "Excel.Application") 'Point to active excel
application

If Target.Address = "$A$1" Then
Set myworkbook = Excel.Application.Workbooks(wkBookB)
Set myworksheet = myworkbook.Worksheets("Sheet1")
'Here is where the code would go to do what I want to do
myworksheet.Range("C1").Value = "Changed"
End If
End Sub

Of course whatever you want to happen in the "Here is where the code would
go.." area would have to use fully qualified addresses (i.e.
workbook.sheet.cell) to make sure the right book got changed

BAC

 

[suzetter]

Yes, I have executed the code where Target.Address = "$A$1" in WkBook
and it works perfectly. But like I told Krem, I didn't want to have al
that code in WkBookA. The bottom line is that the Private Su
Worksheet_Change(ByVal Target As Range) cannot be triggered unless th
workbook has the focus and in my case WkBookB will not have the focu
because the user will be entering text in A1 in WkBook

 

[Dave Peterson]

I created two new workbooks (book1.xls and book2.xls). Each had a sheet named
Sheet1.

I put this code behind sheet1 of book2.xls:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Hi from worksheet_change: " & Target.Address(external:=True)
End Sub

Then I put this code behind Sheet1 of book1.xls:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myWorkSheet As Worksheet

If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub

Set myWorkSheet = Nothing
On Error Resume Next
Set myWorkSheet = Workbooks("book2.xls").Worksheets("sheet1")
On Error GoTo 0

If myWorkSheet Is Nothing Then
MsgBox "That worksheet isn't available or the workbook isn't open!"
Exit Sub
End If

myWorkSheet.Range("a1").Value = "hi"

End Sub

Then I changed B1 in sheet1 of book1.xls.

I got the msgbox back from the event in book2.xls and the value changed to "hi".

Both my workbooks were open in the same instance of excel.

 

[Stephen Bullen]

The easy way would be to use the Calculate event to check if the value in B1
has changed at all:

Dim mOldB1 As Variant

Private Sub Worksheet_Calculate()
If Me.Range("B1").Value <> mOldB1 Then
mOldB1 = Me.Range("B1").Value

'Do your thing
End If
End Sub

Otherwise, you could use a variable declared WithEvents to hook the events
of SheetA from within the SheetB workbook. Chip's site gives an overview of
how to do this for the Application object, at
http://www.cpearson.com/excel/AppEvent.htm, though in your case, you'd hook
the SheetA object rather than the application.

--
Regards

Stephen Bullen
Take your Excel development to the highest levels
with "Professional Excel Development",
www.oaltd.co.uk/ProExcelDev