Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Can anyone tell me why this macro does not run when cell value changes
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hi Barry,
Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event for another worksheet. You would need a worksheet calculate event. However, to do that you would need to save the value of $A$2 at another out of the way cell and perform a comparison in the start of the calculate event to see if it actually changed. Another way is to place the Worksheet_Change on the Entry sheet using K2 as the target. -- Regards, OssieMac "Barry Lennox" wrote: Can anyone tell me why this macro does not run when cell value changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Because the target cell is a formula, the change event doesn't occur when
cells change during a recalculation. -- Regards, Nigel "Barry Lennox" wrote in message ... Can anyone tell me why this macro does not run when cell value changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hi OssieMac
Thanks I tried K2 on entry sheet idea but that didn't work either K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2) Can you send me code for your first suggestion using cell T2 What formula do I put into T2 Barry "OssieMac" wrote: Hi Barry, Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event for another worksheet. You would need a worksheet calculate event. However, to do that you would need to save the value of $A$2 at another out of the way cell and perform a comparison in the start of the calculate event to see if it actually changed. Another way is to place the Worksheet_Change on the Entry sheet using K2 as the target. -- Regards, OssieMac "Barry Lennox" wrote: Can anyone tell me why this macro does not run when cell value changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hi again Barry,
You don't put any formula into T2. T2 is used by the event code to save the value that is in A2 after each time A2 changes. That way you can then compare the new value in A2 with T2. If changed then save the new value to T2 and run required code. If A2 matches T2 then don't run the code. The following code belongs with the worksheet that has the value in A2 that is being tested. Private Sub Worksheet_Calculate() 'Must disable events otherwise will run again 'when A2 is saved to T2 Application.EnableEvents = False If Range("A2") < Range("T2") Then 'Value has changed Range("T2") = Range("A2") 'Resave new A2 value Call Refresh End If Application.EnableEvents = True End Sub Because you are disabling events, if for any reason the code fails before it turns events back on (can occur during testing) then all events remain turned off until you close and restart xl. Therefore the following little sub is handy to have. Simply keep it in any of your modules and you can run it from the VBA editor to re-enable events if they get inadvertantly turned off due to code failure. To run from the VBA editor just click anywhere in the sub and press F5. Private Sub re_enable_events() Application.EnableEvents = True End Sub -- Regards, OssieMac "Barry Lennox" wrote: Hi OssieMac Thanks I tried K2 on entry sheet idea but that didn't work either K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2) Can you send me code for your first suggestion using cell T2 What formula do I put into T2 Barry "OssieMac" wrote: Hi Barry, Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event for another worksheet. You would need a worksheet calculate event. However, to do that you would need to save the value of $A$2 at another out of the way cell and perform a comparison in the start of the calculate event to see if it actually changed. Another way is to place the Worksheet_Change on the Entry sheet using K2 as the target. -- Regards, OssieMac "Barry Lennox" wrote: Can anyone tell me why this macro does not run when cell value changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hi OssieMac
Thanks. Getting closer. I need to run this with multiple windows open. The only thing is the macro runs on the Sheet "Entry" I have tried naming A2 and T2 as ranges but that didn't work. If I enter "Sheets("Sheet2").Select" in the code it works, but the sheet "Entry" is deselected and I have two copies of "Sheet2" If I add "Sheets("Entry").Select" later in the code, whenever I enter anything in Sheet2 it changes to Entry sheet Barry "OssieMac" wrote: Hi again Barry, You don't put any formula into T2. T2 is used by the event code to save the value that is in A2 after each time A2 changes. That way you can then compare the new value in A2 with T2. If changed then save the new value to T2 and run required code. If A2 matches T2 then don't run the code. The following code belongs with the worksheet that has the value in A2 that is being tested. Private Sub Worksheet_Calculate() 'Must disable events otherwise will run again 'when A2 is saved to T2 Application.EnableEvents = False If Range("A2") < Range("T2") Then 'Value has changed Range("T2") = Range("A2") 'Resave new A2 value Call Refresh End If Application.EnableEvents = True End Sub Because you are disabling events, if for any reason the code fails before it turns events back on (can occur during testing) then all events remain turned off until you close and restart xl. Therefore the following little sub is handy to have. Simply keep it in any of your modules and you can run it from the VBA editor to re-enable events if they get inadvertantly turned off due to code failure. To run from the VBA editor just click anywhere in the sub and press F5. Private Sub re_enable_events() Application.EnableEvents = True End Sub -- Regards, OssieMac "Barry Lennox" wrote: Hi OssieMac Thanks I tried K2 on entry sheet idea but that didn't work either K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2) Can you send me code for your first suggestion using cell T2 What formula do I put into T2 Barry "OssieMac" wrote: Hi Barry, Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event for another worksheet. You would need a worksheet calculate event. However, to do that you would need to save the value of $A$2 at another out of the way cell and perform a comparison in the start of the calculate event to see if it actually changed. Another way is to place the Worksheet_Change on the Entry sheet using K2 as the target. -- Regards, OssieMac "Barry Lennox" wrote: Can anyone tell me why this macro does not run when cell value changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hi again Barry,
I think that I am confused about what you want to occur. I thought that if A2 changes then you want the macro to run. From your last post I am assuming that A2 is on Sheet2. Is this correct or not? I also thought from the formula you posted that Sheet2 A2 changes every time Sheet Entry K2 changes. Is this correct? If you want the macro to run every time Sheet2 A2 changes then if you have placed the macro in Sheet2 then what does it matter what is the active sheet? Did you place the macro in Sheet2 module? You should right click Sheet2 tab and select View Code to ensure you are placing it in the correct module. You said that you have multiple windows open. Do you mean multiple Workbooks open or multiple instances of Excel open? Perhaps you can give me a step by step description as follows. What conditions should trigger the macro? What occurs in the sub Refresh? (Perhaps post the sub) -- Regards, OssieMac "Barry Lennox" wrote: Hi OssieMac Thanks. Getting closer. I need to run this with multiple windows open. The only thing is the macro runs on the Sheet "Entry" I have tried naming A2 and T2 as ranges but that didn't work. If I enter "Sheets("Sheet2").Select" in the code it works, but the sheet "Entry" is deselected and I have two copies of "Sheet2" If I add "Sheets("Entry").Select" later in the code, whenever I enter anything in Sheet2 it changes to Entry sheet Barry "OssieMac" wrote: Hi again Barry, You don't put any formula into T2. T2 is used by the event code to save the value that is in A2 after each time A2 changes. That way you can then compare the new value in A2 with T2. If changed then save the new value to T2 and run required code. If A2 matches T2 then don't run the code. The following code belongs with the worksheet that has the value in A2 that is being tested. Private Sub Worksheet_Calculate() 'Must disable events otherwise will run again 'when A2 is saved to T2 Application.EnableEvents = False If Range("A2") < Range("T2") Then 'Value has changed Range("T2") = Range("A2") 'Resave new A2 value Call Refresh End If Application.EnableEvents = True End Sub Because you are disabling events, if for any reason the code fails before it turns events back on (can occur during testing) then all events remain turned off until you close and restart xl. Therefore the following little sub is handy to have. Simply keep it in any of your modules and you can run it from the VBA editor to re-enable events if they get inadvertantly turned off due to code failure. To run from the VBA editor just click anywhere in the sub and press F5. Private Sub re_enable_events() Application.EnableEvents = True End Sub -- Regards, OssieMac "Barry Lennox" wrote: Hi OssieMac Thanks I tried K2 on entry sheet idea but that didn't work either K2 =INDEX($J$4:$O$75,MATCH($P$6,$O$4:$O$75,0),2) Can you send me code for your first suggestion using cell T2 What formula do I put into T2 Barry "OssieMac" wrote: Hi Barry, Changing the value of Entry!$K$2 will not trigger the Worksheet_Change event for another worksheet. You would need a worksheet calculate event. However, to do that you would need to save the value of $A$2 at another out of the way cell and perform a comparison in the start of the calculate event to see if it actually changed. Another way is to place the Worksheet_Change on the Entry sheet using K2 as the target. -- Regards, OssieMac "Barry Lennox" wrote: Can anyone tell me why this macro does not run when cell value changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Call Refresh End Sub Formula in "A2" is =Entry!$K$2 ("Entry") is anothe worksheet in same workbook Barry |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hi OssieMac I am organising a spreadsheet to help organise a roster for volunteers. A range in Sheet2 has the times when volunteers are available, (the volunteer's name occurs in A2. Sometimes volunteers have variations to their usual plan, this is when I make manual entries. The range has to be refreshed each time a new volunteer is selected. This is why the Macro must run in sheet2 as it refreshes the range of cells with formulae. Entry sheet has the grid of time slots.The information in Entry K2 is changed from a scroll bar in Entry. I have tried reversing things, having the scroll bar in Sheet2 but other problems arise (not relating to this issue) Sheet2!A2 = Entry! K2 I need multiple windows open. The Macro IS in Sheet2 .. Sub Refresh() ' Refreshes Availability data ' Macro recorded 16/02/2009 by Barry Range("L4:Q75").Select Selection.Copy Range("E4").Select ActiveSheet.Paste End Sub Four windows need to be open. I hope this helps clarify things "OssieMac" wrote: Hi again Barry, I think that I am confused about what you want to occur. I thought that if A2 changes then you want the macro to run. From your last post I am assuming that A2 is on Sheet2. Is this correct or not? I also thought from the formula you posted that Sheet2 A2 changes every time Sheet Entry K2 changes. Is this correct? If you want the macro to run every time Sheet2 A2 changes then if you have placed the macro in Sheet2 then what does it matter what is the active sheet? Did you place the macro in Sheet2 module? You should right click Sheet2 tab and select View Code to ensure you are placing it in the correct module. You said that you have multiple windows open. Do you mean multiple Workbooks open or multiple instances of Excel open? Perhaps you can give me a step by step description as follows. What conditions should trigger the macro? What occurs in the sub Refresh? (Perhaps post the sub) -- Regards, OssieMac |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro wont run on cell change
Hello again Barry,
Still not sure that I understand. However, perhaps the following code for your Refresh will help. It is not necessary to select worksheets or ranges when copying and pasting data and therefore the current active worksheet does not change. Note that a space and underscore at the end of a line is a linebreak in an otherwise single line of code. There are some conditions when using line breaks. For example you cannot place a line break in the middle of a string enclosed in double quotes. I use the line breaks when posting code because often long lines break at undesired points when posting on the forum. Sub Refresh() 'Copy and Paste in one line of code 'without selecting the worksheet or range Sheets("Sheet2").Range("L4:Q75").Copy _ Sheets("Sheet2").Range("E4") End Sub Following on from there. If you want to control the macro so that it only runs when Sheet2 of the activeworkbook is the active worksheet then you could modify the event macro as follows. However, be aware that you will need to have some way of changing K2 on entry sheet while sheet2 is the selected sheet otherwise it will not run. Private Sub Worksheet_Calculate() 'Must disable events otherwise will run again 'when A2 is saved to T2 Application.EnableEvents = False If ThisWorkbook.Name = ActiveWorkbook.Name And _ ActiveWorkbook.ActiveSheet.Name = "Sheet2" And _ Range("A2") < Range("T2") Then 'Value has changed Range("T2") = Range("A2") 'Resave new A2 value Call Refresh End If Application.EnableEvents = True End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date format from excel to CVS file wont. Change in CVS wont stay. | Excel Worksheet Functions | |||
Date Wont Change in Excel??! | Excel Worksheet Functions | |||
i change a source and the other cells wont change | Excel Worksheet Functions | |||
Combo box wont change selection? | Excel Programming |