CODEWITHSUNDEEP
excelvba
Reilly Jones
Reilly Jones

Reputation: 1

Applying VBA Macro to one column, specific code

I am working with the following code from https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

I want to change it so that Excel only runs Column A (1). What edit(s) do I need to make?

The macro is to allow multiple selections from a data validation dropdown. I'm getting an error, I think because I have other data validations running on the same sheet, which is formatted as a table.

Data Validation Error is Field Type Information / A2 / Restriction: Value must match one of the listed items 

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Column = 1 Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Upvotes: 0

Views: 1377

Answers (1)

Tim von Ahsen
Tim von Ahsen

Reputation: 21

You check that Target starts in Column A. But it could extend to multiple columns, e.g. A1:C10. To refer to just the first column, you could use Target.Columns(1), but others point out that the code might misbehave if you select more than one cell. So, I suggest either

A) After If Target.Column = 1 Then, put a line Set Target = Target.Cells(1)
Or
B) Modify If Target.Column = 1 Then to If Target.Column = 1 And Target.Cells.Count = 1 Then

Option A performs your action for just the first cell in the Target range. Option B causes your code to trigger only if a single cell is being edited. A third option is to loop through all cells in Target.

Upvotes: 1

Related Questions