Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
You should be able to use the same code in WkBookA and use 'I Target.Address = "$A$1" Then' instead -- Kre ----------------------------------------------------------------------- Krem's Profile: http://www.excelforum.com/member.php...fo&userid=2434 View this thread: http://www.excelforum.com/showthread.php?threadid=37943 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
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 -- suzette ----------------------------------------------------------------------- suzetter's Profile: http://www.excelforum.com/member.php...nfo&userid=707 View this thread: http://www.excelforum.com/showthread.php?threadid=37943 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
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 -- suzette ----------------------------------------------------------------------- suzetter's Profile: http://www.excelforum.com/member.php...nfo&userid=707 View this thread: http://www.excelforum.com/showthread.php?threadid=37943 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
execute code in one worksheet when change occurs in another
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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to determine where a change in table occurs | Excel Discussion (Misc queries) | |||
conditional cell shading when a change occurs | Excel Discussion (Misc queries) | |||
Why does this code take so long to execute. | Excel Programming | |||
When does Code Execute on a List Box? | Excel Programming | |||
execute code for all 12 months | Excel Programming |