Worksheet_SelectionChange Event

[G R E G]

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

 

[Tom Ogilvy]

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?).

 

[G R E G]

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

 

[Tom Ogilvy]

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.

 

[G R E G]

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.

 

[steve]

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)