Data Validation Multi Select Code needs boosting

excelakos

Board Regular
Joined
Jan 22, 2014
Messages
81
Hello to everybody

I have found the below very useful code from Contextures Excel Resources to Help You Succeed

VBA Code:
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
If Target.Count > 1 Then GoTo exitHandler

lType = Target.Validation.Type
If lType = 3 Then
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 6 Then
        If oldVal = "" Then
            'do nothing
        Else
            If newVal = "" Then
                'do nothing
            Else
                On Error Resume Next
                Ar = Split(oldVal, ", ")
                strVal = ""
                For i = LBound(Ar) To UBound(Ar)
                    Debug.Print strVal
                    Debug.Print CStr(Ar(i))
                    If newVal = CStr(Ar(i)) Then
                        'do not include this item
                        strVal = strVal
                        lCount = 1
                    Else
                        strVal = strVal & CStr(Ar(i)) & ", "
                    End If
                Next i
                If lCount > 0 Then
                    Target.Value = Left(strVal, Len(strVal) - 2)
                Else
                    Target.Value = strVal & newVal
                End If
                
                  
                
            End If
        End If
    End If
 End If

exitHandler:
  Application.EnableEvents = True
End Sub

Please notice the part where it states for which column we get this function. In this example the code works for column 6 or F


VBA Code:
Target.Value = newVal
    If Target.Column = 6 Then
        If oldVal = "" Then
            'do nothing


What I need to have, is to make it work for multiple columns, lets say column 7 & 8 (or G & H)

Is this feat possible??

Thank you in advance
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Instead of the If statement (If Target.Column = 6 Then) use the Select Case statement.

VBA Code:
   Select Case Target.Column
    Case 6, 7, 8
        If oldVal = "" Then
            'do nothing
 
Upvote 1
Solution

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top