Applying VBA Macro to one column, specific code

Question

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

If Target.Column = 1 already restricts it to ColA. If you're getting an error it's useful to describe what the error is and where and when it occurs. — Tim Williams
– Tim Williams, Commented May 27, 2020 at 20:29
What is the error and on which line? I suspect Target is a multi-cell rangel when you get the error, because the code does not at all handle multi-cell range change events. — Scott Holtzman
– Scott Holtzman, Commented May 27, 2020 at 20:30
Field Type Information / A2 / Restriction: Value must match one of the listed items... so error is data validation, not VBA @TimWilliams — Reilly Jones
– Reilly Jones, Commented May 27, 2020 at 20:37
Field Type Information / A2 / Restriction: Value must match one of the listed items... so error is data validation, not VBA @ScottHoltzman — Reilly Jones
– Reilly Jones, Commented May 27, 2020 at 20:41
I've applied the Data Validation with the multi-select code to many cells on a single worksheet. The multi-select is only allowed in Column A -- so the code is working for me. I have not been able to reproduce the error. I've even created a table with some and with all columns in the table using the same and different validation lists. Still not getting the same (or any) error. There's something else going on... — PeterT
– PeterT, Commented May 27, 2020 at 21:46

Tim von Ahsen · Accepted Answer · 2020-05-27 23:34:19Z

1

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.

answered May 27, 2020 at 23:34

Tim von Ahsen

215 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Applying VBA Macro to one column, specific code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related