Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Adding a Macro to Data Validation

0

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.

excelmacros-vbadata-validation
MS Office Version: 2016
posted by: SarahBear on Thu Jan 25, 2018 at 7:24 am
Answer
Discuss

Answers

0

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

posted by: queue (rep: 467) on Thu Jan 25, 2018 at 11:13 am
Discuss
0

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
posted by: Variatus (rep: 4889) on Thu Jan 25, 2018 at 10:14 pm
Discuss


Answer the Question

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