------ Register to get rid of these "In Post" ads! ------
I am fairly new to
VB and don't know how to code in
VB. I want to accomplish multiple selection from a dropdown list in Excel and also add items in the dropdown list by entering them in the dropdown menu cell. I have the following independent codes to accomplish this:
Code for multiple selection code -
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 5 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Code for adding items in the dropdown menu by typing them in the cell -'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/members-excel-vba-programming/156375-two-independent-vbulletin-codes-not-working-harmony.html#post566478
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Application.WorksheetFunction.CountIf(Range("Options"), [A1]) = 0 Then
Range("AA" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A1").Value
Columns("AA:AA").Sort Key1:=Range("AA1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End If
End If
End Sub
When I paste both these codes after opening "View Code" for a sheet, I am not able to run the data validation list on that sheet as it results into compilation errors. How can I edit the codes to have the functionality I intend to achieve? Even better, could someone help me figure out a way for using these codes for the whole workbook instead of pasting them for each worksheet, as I have 40 worksheets and many workbooks? I am a newbie, so I would greatly appreciate detailed instructions. Thank you.