Selection Change with adding validation message

[Nichole09]

Hello,

I am hoping one of you experts can help me solve this. I am trying to add a worksheet change selection to delete the current data validation input message on this specific selected cell (K7). Then add a new one. The reason for this, is the conditions might change based on other cells so I want the currently input message to be deleted and the valid one be entered. This is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As range)
Dim ws As Worksheet

Application.EnableEvents = False

Set ws = Sheets("Status of Closing")

If Target.Address = ws.range("K7") Then
With ws.range("K7").Validation
.Delete
 .Add Type:=xlValidateInputOnly
 .InputTitle = "Escrow Calculations:"
 .InputMessage = ws.range("Ap5").value & Space(10) & Space(10) & "Tax Installments: " & ws.range("AR3").value & ws.range("AS3").value & Space(10) & ws.range("AT3").value & ws.range("AU3").value
End With
Else
ws.range("K7").Validation.Delete
End If



Application.EnableEvents = True

End Sub

However, it doesn't work. I have added Application.EnableEvents = True to the immediate window, saved and closed. It still doesn't work. If I mess with the code, and run it line by line in another module. It works. But not if I simply select K7 like the code should run just by the selection. Any help would be much appreciated! Thank you!

 

[6StringJazzer]

Your problem is here:
If Target.Address = ws.range("K7") Then
Target.Address is a string that will look like "$K$7". However, what you are comparing it to is a Range, not a string. Try this:
If Target.Address = "$K$7" Then

(Also: When you say that something "doesn't work" please tell us what is actually happening--nothing? error messages? Excel crashes? other? In this case I was able to deduce it from looking at the code, but that is not always possible.)

 

[Nichole09]

Hi 6StringJazzer! I appreciate your help! Sorry for the lack of detail. I tried your solution and this doesn't seem to work either. What I mean by that is, nothing happens. No error message. Just nothing happens when I click on cell K7.

 

[Nichole09]

I just tried this line instead of target.address like I had and now it works! Thank you for your help!

If Not Intersect(Target, Target.Worksheet.range("k7")) Is Nothing Then

 

[6StringJazzer]

There is something I didn't pick up the first time I looked at your code. You are using this code to work with a sheet called "Status of Closing". What sheet module contains this code?