Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_SelectionChange Event
Hi,
The code below does not work for some reason. This procedure is in worksheet "Sheet1" Range("g46:h46") is two merged cells, perhaps this is the reason? It is validated to have either "yes" or "no" Thank you, Greg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Selection, Range("g46:h46")) Is Nothing Then Exit Sub If Not Intersect(Selection, Range("g46:h46")) Is Nothing Then If Range("g46") = "NO" Then Sheets("Detail").Activate Sheets("Detail").Range("b13:A43").BorderAround _ ColorIndex:=3, Weight:=xlThick MsgBox "You may overwrite dates here" ,, "Optional" Sheets("Detail").Range("b13:A43").Borders.LineStyl e = xlLineStyleNone Sheets("Sheet1").Activate End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_SelectionChange Event
Code worked fine for me. If I select the merged cells (either G46 or H46)
the macro executes. What do you mean by does not work. Do you get an error. Does it not execute (are events enabled?). -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi, The code below does not work for some reason. This procedure is in worksheet "Sheet1" Range("g46:h46") is two merged cells, perhaps this is the reason? It is validated to have either "yes" or "no" Thank you, Greg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Selection, Range("g46:h46")) Is Nothing Then Exit Sub If Not Intersect(Selection, Range("g46:h46")) Is Nothing Then If Range("g46") = "NO" Then Sheets("Detail").Activate Sheets("Detail").Range("b13:A43").BorderAround _ ColorIndex:=3, Weight:=xlThick MsgBox "You may overwrite dates here" ,, "Optional" Sheets("Detail").Range("b13:A43").Borders.LineStyl e = xlLineStyleNone Sheets("Sheet1").Activate End If End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_SelectionChange Event
Hi Tom,
Thanks for looking. It did execute when I move to to this range from other cells. However, when I play with validation drop down changing it from "Yes" to "No" nothing is happening. How would I program for this? Thank you, Greg -----Original Message----- Code worked fine for me. If I select the merged cells (either G46 or H46) the macro executes. What do you mean by does not work. Do you get an error. Does it not execute (are events enabled?). -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi, The code below does not work for some reason. This procedure is in worksheet "Sheet1" Range("g46:h46") is two merged cells, perhaps this is the reason? It is validated to have either "yes" or "no" Thank you, Greg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Selection, Range("g46:h46")) Is Nothing Then Exit Sub If Not Intersect(Selection, Range("g46:h46")) Is Nothing Then If Range("g46") = "NO" Then Sheets("Detail").Activate Sheets("Detail").Range("b13:A43").BorderAround _ ColorIndex:=3, Weight:=xlThick MsgBox "You may overwrite dates here" ,, "Optional" Sheets("Detail").Range("b13:A43").Borders.LineStyl e = xlLineStyleNone Sheets("Sheet1").Activate End If End If End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_SelectionChange Event
Selectionchange fines when you change from one cell to another.
In Excel 2000 and later, the Change (not selectionchange) event will fire if you choose a new value in a dropdown. In xl97, you are out of luck. -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi Tom, Thanks for looking. It did execute when I move to to this range from other cells. However, when I play with validation drop down changing it from "Yes" to "No" nothing is happening. How would I program for this? Thank you, Greg -----Original Message----- Code worked fine for me. If I select the merged cells (either G46 or H46) the macro executes. What do you mean by does not work. Do you get an error. Does it not execute (are events enabled?). -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi, The code below does not work for some reason. This procedure is in worksheet "Sheet1" Range("g46:h46") is two merged cells, perhaps this is the reason? It is validated to have either "yes" or "no" Thank you, Greg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Selection, Range("g46:h46")) Is Nothing Then Exit Sub If Not Intersect(Selection, Range("g46:h46")) Is Nothing Then If Range("g46") = "NO" Then Sheets("Detail").Activate Sheets("Detail").Range("b13:A43").BorderAround _ ColorIndex:=3, Weight:=xlThick MsgBox "You may overwrite dates here" ,, "Optional" Sheets("Detail").Range("b13:A43").Borders.LineStyl e = xlLineStyleNone Sheets("Sheet1").Activate End If End If End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_SelectionChange Event
Thank you very much for your explanation, Tom.
I am on 97, but at least I know now that it is not going to work. -----Original Message----- Selectionchange fines when you change from one cell to another. In Excel 2000 and later, the Change (not selectionchange) event will fire if you choose a new value in a dropdown. In xl97, you are out of luck. -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi Tom, Thanks for looking. It did execute when I move to to this range from other cells. However, when I play with validation drop down changing it from "Yes" to "No" nothing is happening. How would I program for this? Thank you, Greg -----Original Message----- Code worked fine for me. If I select the merged cells (either G46 or H46) the macro executes. What do you mean by does not work. Do you get an error. Does it not execute (are events enabled?). -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi, The code below does not work for some reason. This procedure is in worksheet "Sheet1" Range("g46:h46") is two merged cells, perhaps this is the reason? It is validated to have either "yes" or "no" Thank you, Greg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Selection, Range("g46:h46")) Is Nothing Then Exit Sub If Not Intersect(Selection, Range("g46:h46")) Is Nothing Then If Range("g46") = "NO" Then Sheets("Detail").Activate Sheets("Detail").Range("b13:A43").BorderAround _ ColorIndex:=3, Weight:=xlThick MsgBox "You may overwrite dates here" ,, "Optional" Sheets("Detail").Range ("b13:A43").Borders.LineStyle = xlLineStyleNone Sheets("Sheet1").Activate End If End If End Sub . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_SelectionChange Event
Greg,
You can set up a (hidden) formula somewhere on your sheet (or workbook). With the formula dependent on your dropdown. When the drop down is changed a calculate event will fire which can be captured in a Calculate event macro. (should work in 97) -- sb "G R E G" <Greg@*.* wrote in message ... Thank you very much for your explanation, Tom. I am on 97, but at least I know now that it is not going to work. -----Original Message----- Selectionchange fines when you change from one cell to another. In Excel 2000 and later, the Change (not selectionchange) event will fire if you choose a new value in a dropdown. In xl97, you are out of luck. -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi Tom, Thanks for looking. It did execute when I move to to this range from other cells. However, when I play with validation drop down changing it from "Yes" to "No" nothing is happening. How would I program for this? Thank you, Greg -----Original Message----- Code worked fine for me. If I select the merged cells (either G46 or H46) the macro executes. What do you mean by does not work. Do you get an error. Does it not execute (are events enabled?). -- Regards, Tom Ogilvy "G R E G" <Greg@*.* wrote in message ... Hi, The code below does not work for some reason. This procedure is in worksheet "Sheet1" Range("g46:h46") is two merged cells, perhaps this is the reason? It is validated to have either "yes" or "no" Thank you, Greg Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Selection, Range("g46:h46")) Is Nothing Then Exit Sub If Not Intersect(Selection, Range("g46:h46")) Is Nothing Then If Range("g46") = "NO" Then Sheets("Detail").Activate Sheets("Detail").Range("b13:A43").BorderAround _ ColorIndex:=3, Weight:=xlThick MsgBox "You may overwrite dates here" ,, "Optional" Sheets("Detail").Range ("b13:A43").Borders.LineStyle = xlLineStyleNone Sheets("Sheet1").Activate End If End If End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where?Worksheet code module or Worksheet_SelectionChange event han | Excel Worksheet Functions | |||
Worksheet_SelectionChange Problem | Excel Discussion (Misc queries) | |||
Worksheet_SelectionChange not working | New Users to Excel | |||
Re : Excel event handler Worksheet_SelectionChange | Excel Programming | |||
Re : Excel event handler Worksheet_SelectionChange | Excel Programming |