The Code Cage Forums - View Single Post - Two independent VB codes not working in harmony.

incisivekeith · #1 (**permalink**) 23rd November 2009, 02:44 AM

------ 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 -

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.