Adding a Macro to Data Validation


I've just watched the YouTube video about adding a message box via a macro to a data validation list and was after some additional help with it.

The code works perfectly for the one cell I have named in the range but I want to able to apply the macro to all of the cells within my worksheet that have data validation on them. 

I've tried naming all of the cells in the range like this;

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range ("C12, C13, C14, C17, C18, C19, C20")
Case "No"
MsgBox "Remember to add a reason!"
End Select
End Sub

That doesn't seem to work though and the message box only comes up on the other cells if the very first cell has 'No' selected.

Is it possible to apply the code to multiple cells?  Any help or idea would be greatly appreciated.




An Example would help and referencing the url to the video, However, the first thing to check is your data validation - Specifically its RANGE.  It would be benificial to use a NAMED range for what I'm guessing you have a CUSTOM list of values?

Secondly, one you have NAMED range, you could use it in your VBA code (instead of listing out the indiv


I have modified the syntax of your code. You can add as many ranges - also single cells - as you may require.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 26 Jan 2018
    Dim Sp() As String
    Dim Rng As Range
    Dim i As Integer
    Sp = Split("C12:C14,C17:C20", ",")
    Set Rng = Range(Sp(0))
    For i = 1 To UBound(Sp)
        Set Rng = Application.Union(Rng, Range(Sp(i)))
    Next i
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        If Target.Value = "No" Then
            MsgBox "Remember to add a reason.", _
                   vbExclamation, "Reminder"
        End If
    End If
End Sub

Answer the Question

You must create an account to use the forum. Create an Account or Login