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" 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