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.



suzetter wrote:

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

--
suzetter
------------------------------------------------------------------------
suzetter's Profile: http://www.excelforum.com/member.php...fo&userid=7078
View this thread: http://www.excelforum.com/showthread...hreadid=379438

--

Dave Peterson