To upload a workbook, click reply then add your few words, scroll down past the submit button and you will see the Manage Attachments button, this is where you get to add files for upload, if you have any trouble please use this Contact Us link or the one at the bottom of the any page.

Simon Lloyd · #2 (**permalink**) 23rd November 2009, 03:19 AM

------ Register to get rid of these "In Post" ads! ------

Why not add a workbook?
Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query

Attachments.

Information

To upload a workbook, click reply then add your few words, scroll down past the submit button and you will see the Manage Attachments button, this is where you get to add files for upload, if you have any trouble please use this Contact Us link or the one at the bottom of the any page.

that said, this code is just your 2 codes put together in a WORKBOOK event code module, get rid of your other worksheet code and save this code as instructed:

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''Code for multiple selection code
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
''Code for adding items in the dropdown menu by typing them in the cell
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

How to Save a Workbook Event Macro
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/members-excel-vba-programming/156375-two-independent-vbulletin-codes-not-working-harmony.html#post566492

Help

1. Copy the macro above placing the cursor to the left of the code box hold the CTRL & Left Click, then Right Click selected code and Copy.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S