Selection Change with adding validation message

Nichole09

Board Regular
Joined
Aug 27, 2016
Messages
122
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:

VBA 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

Active Member
Joined
Jan 27, 2010
Messages
264
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

Board Regular
Joined
Aug 27, 2016
Messages
122
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

Board Regular
Joined
Aug 27, 2016
Messages
122
I just tried this line instead of target.address like I had and now it works! Thank you for your help!

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

6StringJazzer

Active Member
Joined
Jan 27, 2010
Messages
264
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?
 

Forum statistics

Threads
1,078,013
Messages
5,337,725
Members
399,167
Latest member
Bellzebub

Some videos you may like

This Week's Hot Topics

Top