Macro newbie.....

[CMDUCLan]

Hi, having never used Macros before I wanted to use one to be able to multi-select options from a dropdown list in Column C. Having googled, some kind person had posted the VBA code and I opened the VBA editor, pasted it in and hey presto it worked! (See code below question).

Anyway, I then updated the Dropdown list in my data validation and although the previous rows still allow me to multi-select, the blank rows don't (I updated the drop down box for the entire column from Row 2 downwards to about row 1100, although only 600 currently in use. What have I done wrong? It seems like the Macro is still working for some rows but not for the new ones?

Thanks in hope.
Colette


Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Column = 3 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

 

[footoo]

Try this :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue$, Newvalue$
On Error GoTo e
If Target.Column = 3 And Target.Cells.Count = 1 Then
    If Intersect(Target, Cells.SpecialCells _
        (xlCellTypeAllValidation)) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    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
e: Application.EnableEvents = True
End Sub

 

[CMDUCLan]

Thank you. I will try that. I was thinking of doing the 'multiple items selection' on a couple of other columns in the same spreadsheet. If I did that, would I paste the code below the code in the window or open an entirely new window?

 

[footoo]

Thank you. I will try that. I was thinking of doing the 'multiple items selection' on a couple of other columns in the same spreadsheet. If I did that, would I paste the code below the code in the window or open an entirely new window?

I don't understand. Please explain further.

 

[CMDUCLan]

When I got to the VBA editor (not sure I'm using the right term there), I didn't know if I needed to open a new 'window' within that for each VBA instruction, so to speak? Not sure if I can paste a screen shot in here?

 

[footoo]

In the VBE (Visual Basic Editor), you can put a macro in the same "Module" as other macros or in a separate Module.
To add a Module : Insert / Module
If you want to re-name a Module : View / Properties Window , select the Module and in the (Name) box of the Properties Window type the new name and press Enter