|
Worksheet Change Event
I thare any way to execute more than one worksheet change per worksheet; say
for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
Sure, something like
Private Sub Worksheet_Change(ByVal Target As Range) if target.address=range("a1") then msgbox "a1") if target.address=range("e1") then msgbox "e1") end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Tony S." wrote in message ... I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
Maybe this
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
Hi Don... thanks for your prompt respose. I'm kinda new at programming so I
could use a little more guidance if you please. Here is my revised code: I get an "End If without block If" error Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then If Target.Address = Range("a1") Then MLV_wildcard_cell End If If Target.Address = Range("e1") Then ERM_wildcard_cell End If End Sub I'm sure you can immediately see what is wrong, but I don't have a clue. Tony "Don Guillett" wrote: Sure, something like Private Sub Worksheet_Change(ByVal Target As Range) if target.address=range("a1") then msgbox "a1") if target.address=range("e1") then msgbox "e1") end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Tony S." wrote in message ... I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
Hi Mike, Thanks for your reply.
Perhaps I was unclear with my first post. My apologies. I want to run 2 different macros depending on the value entered in each cell. "A1" would run Macro1 and E1 would run Macro2. Hope this clears things up. "Mike H" wrote: Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
You almost had it on your own in your response to Don's reply earlier.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell ... some code here Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell ... your code for a change in E1 here End If End Sub Note that Target.Address returns the string representation of the address, complete with the absolute indicators (dollar signs). Hope this helps. "Tony S." wrote: Hi Mike, Thanks for your reply. Perhaps I was unclear with my first post. My apologies. I want to run 2 different macros depending on the value entered in each cell. "A1" would run Macro1 and E1 would run Macro2. Hope this clears things up. "Mike H" wrote: Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
Hi JLatham, thank you for replying. I still get an "End If without block
If" error when I run this... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell End If End Sub I'm trying to get the option (not necessarily mandatory) to input into either "A1" or "E1" and then run respective macros. Please forgive my ignorance. Any idea what my prolblem is? Thanks! "JLatham" wrote: You almost had it on your own in your response to Don's reply earlier. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell ... some code here Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell ... your code for a change in E1 here End If End Sub Note that Target.Address returns the string representation of the address, complete with the absolute indicators (dollar signs). Hope this helps. "Tony S." wrote: Hi Mike, Thanks for your reply. Perhaps I was unclear with my first post. My apologies. I want to run 2 different macros depending on the value entered in each cell. "A1" would run Macro1 and E1 would run Macro2. Hope this clears things up. "Mike H" wrote: Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
If Target.Address = "$A$1" Then MLV_wildcard_cell should be If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub End If If Target.Address = "$E$1" Then ERM_wildcard_cell should be If Target.Address = "$E$1" Then ERM_wildcard_cell End If Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 17 Feb 2009 13:43:01 -0800, Tony S. wrote: Hi JLatham, thank you for replying. I still get an "End If without block If" error when I run this... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell End If End Sub I'm trying to get the option (not necessarily mandatory) to input into either "A1" or "E1" and then run respective macros. Please forgive my ignorance. Any idea what my prolblem is? Thanks! "JLatham" wrote: You almost had it on your own in your response to Don's reply earlier. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell ... some code here Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell ... your code for a change in E1 here End If End Sub Note that Target.Address returns the string representation of the address, complete with the absolute indicators (dollar signs). Hope this helps. "Tony S." wrote: Hi Mike, Thanks for your reply. Perhaps I was unclear with my first post. My apologies. I want to run 2 different macros depending on the value entered in each cell. "A1" would run Macro1 and E1 would run Macro2. Hope this clears things up. "Mike H" wrote: Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
That's what I was looking for Chip. Thank you and everyone else who helped.
I'm sure they would have reached the same solution, had I been more detailed in my request. "Chip Pearson" wrote: If Target.Address = "$A$1" Then MLV_wildcard_cell should be If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub End If If Target.Address = "$E$1" Then ERM_wildcard_cell should be If Target.Address = "$E$1" Then ERM_wildcard_cell End If Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 17 Feb 2009 13:43:01 -0800, Tony S. wrote: Hi JLatham, thank you for replying. I still get an "End If without block If" error when I run this... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell End If End Sub I'm trying to get the option (not necessarily mandatory) to input into either "A1" or "E1" and then run respective macros. Please forgive my ignorance. Any idea what my prolblem is? Thanks! "JLatham" wrote: You almost had it on your own in your response to Don's reply earlier. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell ... some code here Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell ... your code for a change in E1 here End If End Sub Note that Target.Address returns the string representation of the address, complete with the absolute indicators (dollar signs). Hope this helps. "Tony S." wrote: Hi Mike, Thanks for your reply. Perhaps I was unclear with my first post. My apologies. I want to run 2 different macros depending on the value entered in each cell. "A1" would run Macro1 and E1 would run Macro2. Hope this clears things up. "Mike H" wrote: Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
Chip. I goofed in that I did NOT add .address after. Both of these test the
same result. Private Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("e1").Address Then MsgBox "hi" If Target.Address = "$E$1" Then MsgBox "hi" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Chip Pearson" wrote in message ... If Target.Address = "$A$1" Then MLV_wildcard_cell should be If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub End If If Target.Address = "$E$1" Then ERM_wildcard_cell should be If Target.Address = "$E$1" Then ERM_wildcard_cell End If Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 17 Feb 2009 13:43:01 -0800, Tony S. wrote: Hi JLatham, thank you for replying. I still get an "End If without block If" error when I run this... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell End If End Sub I'm trying to get the option (not necessarily mandatory) to input into either "A1" or "E1" and then run respective macros. Please forgive my ignorance. Any idea what my prolblem is? Thanks! "JLatham" wrote: You almost had it on your own in your response to Don's reply earlier. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MLV_wildcard_cell ... some code here Exit Sub ' to keep from testing for E1 which didn't happen End If If Target.Address = "$E$1" Then ERM_wildcard_cell ... your code for a change in E1 here End If End Sub Note that Target.Address returns the string representation of the address, complete with the absolute indicators (dollar signs). Hope this helps. "Tony S." wrote: Hi Mike, Thanks for your reply. Perhaps I was unclear with my first post. My apologies. I want to run 2 different macros depending on the value entered in each cell. "A1" would run Macro1 and E1 would run Macro2. Hope this clears things up. "Mike H" wrote: Maybe this Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1,E1")) Is Nothing Then MsgBox "You changed " & Target.Address ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub Mike "Tony S." wrote: I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
Worksheet Change Event
My response to Chip
Chip. I goofed in that I did NOT add .address after. Both of these test the same result. Private Sub Worksheet_Change(ByVal Target As Range) 'If Target.Address = Range("e1").Address Then MsgBox "hi" If Target.Address = "$E$1" Then MsgBox "hi" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Tony S." wrote in message ... Hi Don... thanks for your prompt respose. I'm kinda new at programming so I could use a little more guidance if you please. Here is my revised code: I get an "End If without block If" error Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then If Target.Address = Range("a1") Then MLV_wildcard_cell End If If Target.Address = Range("e1") Then ERM_wildcard_cell End If End Sub I'm sure you can immediately see what is wrong, but I don't have a clue. Tony "Don Guillett" wrote: Sure, something like Private Sub Worksheet_Change(ByVal Target As Range) if target.address=range("a1") then msgbox "a1") if target.address=range("e1") then msgbox "e1") end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Tony S." wrote in message ... I thare any way to execute more than one worksheet change per worksheet; say for cells "A1" and "E1"? Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause an alert when they are changed. Set KeyCells = Range("A1") 'another code for cell E1?? If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then ' Display a message when one of the designated cells has been ' changed. ' Place your code here. End If End Sub |
| All times are GMT +1. The time now is 11:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com