+ Reply to Thread
Results 1 to 2 of 2

Help with If Target.Column - Multiple Columns

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Help with If Target.Column - Multiple Columns

    Hi All, I am new to VBA in excel and I cannot find an answer anywhere on google. I'm attempting to have a drop down box in multiple columns that allow me to choose multiple values separated by a semi colon. I found this code but I cannot get it to adapt to multiple columns. I do not take credit for the original creation of this VBA - just trying to adapt it to my means.

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim lUsed As Long
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Intersect(Target, rngDV) Is Nothing Then
    'do nothing
    Application.EnableEvents = False
    newVal = Target.Value
    oldVal = Target.Value
    Target.Value = newVal
    If Target.Column = 25 Then
    If oldVal = "" Then
    'do nothing
    If newVal = "" Then
    'do nothing
    lUsed = InStr(1, oldVal, newVal)
    If lUsed > 0 Then
    Target.Value = oldVal
    Target.Value = oldVal _
    & ", " & newVal
    End If

    End If
    End If
    End If
    End If

    Application.EnableEvents = True
    End Sub

    I added If Target.Column = 25 or 27 but that just blew up the entire sheet. I just want to apply this bit of code to two columns... Any help would be greatly appreciated!

  2. #2
    Forum Contributor pareshj's Avatar
    Join Date
    MS-Off Ver
    2007 & 2010

    Re: Help with If Target.Column - Multiple Columns


    You can use Select Case optiion, check the below code :

    Please Login or Register  to view this content.

    Paresh J
    Click on "* Add Reputation" as a way to say thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  2. [SOLVED] Target multiple columns in a code
    By boboivan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-31-2014, 06:40 AM
  3. [SOLVED] Need alternate syntax 'Select Case' on Target.Column and Target.Row at the same time.
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2012, 11:19 AM
  4. Bar chart with 'target' lines but target varies per column
    By Ineedcoffee in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 11-24-2011, 05:41 AM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1