How to merge two codes to allow (2) functionalities

[ernestobudget]

[h=1]How to merge two codes too allow (2) functionalities[/h]

I

I have a rather complex situation that I'm not sure how to handle. First let me provide some background on what I have accomplished thus far.

Background:

I have an excel file with two worksheets. Worksheet two, named Data, is where all my data is located. It begins with A1 going through JC4 (A1:JC4). However, for the purpose of my project the data I'm concerned with starts on E1 through JC4. Also, every other column is formatted with ="".
I have used the following sequence of commands, obtained from a website, to make it much simpler to define name ranges:

1. Select the worksheet ‘Data’
2. Select cell A1
3. Select the current region using CTRL + SHIFT + 8
4. Press CTRL + G to bring up the Go To dialog box
5. Click on the “Special” button to bring up the Go To Special dialog box
6. Select “Constants” and press the ENTER key (or click the “OK” button)
7. Now define the Named Ranges using your selection by pressing CTRL + SHIFT + F3
8. Excel will ask you to “Create names from values in the:”
9. Make sure only “Top row” is selected, then press the ENTER key (or click the “OK” button)
10. Check that the correct named ranges have been defined by opening the Name Manager with CTRL + F3

While I have defined named ranges with the above steps, this is not the same as a dynamic named range. At least I don't think its the same thing. I have also protected the worksheet to prevent anyone from changing the formatting. Note: the top row of each column that contains information has been designated as the name for the information that comes after it (see step 8 and 9 above). Now, my first worksheet is where I have created conditional drop down screen by using data validation. The drop down screen are conditionally formatted. Meaning depending on the selection from the drop down screen in cell E27 will determine the list on the drop down screen in cell F27, and so on through cell H27. Furthermore, the drop down items on:

1) Cell E27 (in the first worksheet) are located on the second worksheet in cells E1, G1, I1, K1. Each of those columns have a list of items that fall under them.

The lay out of the data worksheet is too combursome to list out, but I have attached a picture. IF you follow the data you will see some of the information is listed in two cells, for conditional formatting purposes. I have also attached a picture of worksheet 1 with the drop down screens.


Code 1: (allows the selection of multiple items in one cell separated by a comma)

Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
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 = 7 Or 8 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub

Code 2: (allows for manually adding directly to the drop down list a new item that is not on the list. It will then add it to the list after the hard entry directly to the drop down menu). Remember the list will be on the Data worksheet, worksheet #2. The drop down menus are on the first worksheet).

' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"), Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub


What I have done thus far:

I have added the first code by right clicking on the worksheet and viewing the code, then adding Code 1. It works beautifully.

What I want to do next:

What I'm trying to figure out is how to add Code 2 to the mix with out unding Code 1. I'm trying to either combine the codes or insert the second code without disrupting Code 1. Remember I have already created a defined name range in my Data worksheet for all my columns/information. I tried deleting Code 1 and only inserting Code 2 but it didnt work. Code 2 does not work based on how I have designed the formatting. How do I apply Code 2 to what I have done thus far? Can this be done by bypassing a dynamic name range, since I have already assigned a defined name range? If not how do I go about tackling this issue? I want both codes to apply to Columns E, F, G, and H on the first worksheet.

Thanks for your assistance.

 

[StephenCrump]

You say you are happy with Code block 1, so I have focussed on Code block 2. This code is from another source, and is quite specific in what it does. It is not clear how this relates to what you want to do?

My guess is that all you want is the ability to add new entries to each Data Validation list, i.e. to make each validation list dynamic? One way to do this, without VBA, is:

1. Create an Excel Table for each list
2. Define a range name for each list, and point it to the Table.
3. Use that name as the list in data validation.

Here's a link to a more detailed explanation on Chandoo.org

Make your data validations dynamic! [quick tip] | Chandoo.org - Learn Microsoft Excel Online