Dynamic Named Ranges and Dynamic Data Validation

[gijimbo]

I have a sheet in a workbook that contains a list of categories for recipes (like chicken,beef,italian,mexican,chinese,american,etc...).

In that same sheet there is a list for each of the items in the categories list. These lists contain references to recipes that fit under that specific category.

These named lists are dynamic as values (recipes and categories) can be added to or taken away from them based on text files that are used to update (add to) the sheet.

Another sheet references this named "categories" list via data validation.
Depending what category is selected in this first data validation cell, I have a second data validation cell that references the list for the selected category.

The code I have works for this so far (it's pretty simple due to the named range naming convention I used) but would like the value in the cell to default to the first item (recipe) in that category.

Here's the code that works:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim list As String
Dim lname As String

    'Get the name of the range that the
    'data validation cell is to refer to
    lname = "Type" & Range("C2").Value & "List"
    list = "=" & lname
    'MsgBox (list)
    Range("C3").Validation.Delete
    Range("C3").Validation.Add _
        Type:=xlValidateList, _
    Formula1:=list
End Sub

I've tried adding the following just before the 'End Sub' line but none work.
Can someone tell me how to reference the first cell in a named range?

Range("C4").Value = Range(lname).Value
Range("C4").Value = Index(lname, 1, 1)
Range("C4").Value = Index(lname, 1)
Range("C4").Value = Range(lname)(1).Value
Range("C4").Value = Range(lname)(1, 1).Value

Edit: I've also now tried this, but it still doesn't work:

Range("C4").Value = Range(lname).Cells(1).Value

 

[Jerry Sullivan]

From your first code example, it looks like your 2nd validation list (recipies) is in C3; however in your tries at setting the default as the first value in the list, you are trying to change the value of C4.

A few other items that might be giving you problems:
1. When using Event code that might trigger other Events, always temporarily disable and then re-enable events.

2. Do your dynamic ranges have workbook scope or worksheet scope? This affects how they are referenced.

3. It's better not to use "list" as a variable name since it is a reserved word.

4. Your current worksheet_change code runs and updates your validation list every time a cell is changed in your worksheet. Assuming you really only want this to be updated if the change occurs in cell C2, then test the Target parameter to see if it contains C2 before executing the rest of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub

 

[gijimbo]

Thanks for the tips. I still can't get it to reference the first item in the named range though.

Here's what I've got now:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    
    Dim tform As String
    Dim tvar As String

    'Get the name of the range that the
    'data validation cell is to refer to
    tvar = "Type" & Range("C2").Value & "List"
    tform = "=" & tvar
    Range("C3").Validation.Delete
    Range("C3").Validation.Add _
        Type:=xlValidateList, _
        Formula1:=tform
    Range("C3").Value = Range(tvar).Cells(1).Value
End Sub

 

[Jerry Sullivan]

If your dynamic ranges are on another worksheet, you'll need to add that reference.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Then Exit Sub
    Dim tform As String
    Dim tvar As String
    On Error GoTo CleanUp
    Application.EnableEvents = False
 
    'Get the name of the range that the
    'data validation cell is to refer to
    tvar = "Type" & Range("C2").Value & "List"
    tform = "=" & tvar
    Range("C3").Validation.Delete
    Range("C3").Validation.Add _
        Type:=xlValidateList, _
        Formula1:=tform
    Range("C3").Value = Sheets("Sheet2").Range(tvar).Cells(1).Value
CleanUp:
    Application.EnableEvents = True
End Sub

Also the code above shows how to disable and then re-enable events.

 

[gijimbo]

Thanks again, the named ranges were on another sheet as you suspected so everything works great now.

Also thanks for the suggestion about disabling/re-enabling events. I had never considered that before. This will make many of my other workbooks flow MUCH better. I'm always looking to improve my vba skills.