Data validation/drop-down list with same set of options as auto-filter

[Thirith]

I'm afraid I'm new to this forum, so please be gentle! I did a search for this topic but didn't find anything.

I've created a spreadsheet in Excel 2003 that lists the participants of the project I'm working on. One of the fields gives their subject area. At the moment I'm using data validation and drop-down boxes referring to a list on another worksheet to make sure that the subject areas remain consistent. However, every now and then there's a new list item if a participant covers a different subject area.

Obviously I can just add this new subject area to the list on the other worksheet (which is how it works at the moment). The ideal thing, though, would be if you clicked on the field and got a drop-down list equivalent to the auto-filter list, i.e. every type of entry that's been used in the column so far, listed alphabetically. If the users don't find the subject area on that list, they can just enter a new one, and that will then be added automatically to the drop-down list. That way the list on worksheet 2 wouldn't have to be updated manually.

Is there such a function? And, if so, how would I use it?

I hope that my explanation is clear enough.

 

[Andrew Poulsom]

Welcome to the Board.

There is an example you can download here:

http://www.contextures.com/DataValListAddSortMulti.zip

 

[Thirith]

Great, thanks! I'm a stranger to VB but I used to program in a number of different languages (mostly Basic variants, a tiny bit of C++... and C-64 assembler ), so I should be able to figure out the code.

 

[Thirith]

... or so I thought. Guess I'd better invest in a good book on Excel macros and Visual Basic!

 

[Thirith]

My apologies for posting for the third time in a row in my own thread. I've got a couple of specific questions concerning the code in the example you gave me. If the only valid answer is basically RTM!, then I'll go and do so. I tried to find VBA concordances online, but either I didn't find any good ones or I'm too dumb to use them. Any help would be much appreciated.

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)<o:p></o:p>
    On Error GoTo 0<o:p></o:p>
    If rngDV Is Nothing Then Exit Sub<o:p></o:p>
    If Intersect(Target, rngDV) Is Nothing Then Exit Sub

What's the function of these lines? If I understand correctly, it defines the variable rngDV as the range of cells that have data validation. What do the two If lines check, or rather, why are they necessary? Do they check if something was entered in cells other than the ones where you're supposed to enter something?
<o></o>

 Set rng = ws.Range(Cells(1, Target.Column) & "List")
    If Application.WorksheetFunction _
      .CountIf(rng, Target.Value) Then
      Exit Sub

In the first line, I'm not completely clear about the "& "List"" part. rng is set to the first cell in the same column as the target cell (i.e. the cell in which the change happened, right?)... and then extended to the entire list of entries? The quotation marks make me think that this is a named range (which I couldn't find on the worksheet), but I might be completely wrong here.


The If...CountIf command: do I understand correctly that this checks whether the value entered already exists on the list (i.e. rng), and if it does the subroutine is ended?
<o></o>

  Else
      i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
    ws.Cells(i, rng.Column).Value = Target.Value

The variable i is set to the row number of the first empty cell in the column (last filled cell + 1); then the cell in question in the column originally selected receives the new value. In simpler English, the new value is added at the end of the appropriate column
<o></o>

  Set rng = ws.Range(Cells(1, Target.Column) & "List")
      rng.Sort Key1:=ws.Cells(1, rng.Column), _
        Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    End If

And this finally sorts the column with the new entry alphabetically, right?
<o></o>

 

[RoryA]

I've added some comments:

' assign range of validated cells to a variable (within error handler)
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
' clear the On Error Resume Next statement
    On Error GoTo 0
' if there are no validated cells then exit
    If rngDV Is Nothing Then Exit Sub
' if the cell we are checking is not within the range of validated cells, then exit
    If Intersect(Target, rngDV) Is Nothing Then Exit Sub

 Set rng = ws.Range(Cells(1, Target.Column) & "List")

could better be written as:

 Set rng = ws.Range(Cells(1, Target.Column)[B].Value[/B] & "List")

So you take the contents of the cell in row 1 of the target column, append the word "List" to the end, and then look at a named range by that name. So if you were in column B and B1 contained "Country", you would be looking at a range called "CountryList".

 

[Thirith]

Great - thanks for your help! It's given me the push to learn how to do dynamic ranges. I've now got the function to work. Is it okay if I run it by you to check whether there are any major d'oh! bits in it?

I've basically got two macros - one on the participant sheet that checks whether an entered value doesn't exist yet on the list and adds the value to the list if necessary, and one on the list sheet that automatically sorts the list.

Macro 1:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim i As Integer
Dim rng As Range


If Target.Column <> 9 Then Exit Sub


Set ws = Worksheets("Types of Activism")
Set rng = ws.Range("Activism")


If Application.WorksheetFunction.CountIf(rng, Target.Value) Then
   Exit Sub
   Else
      i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
      ws.Cells(i, rng.Column).Value = Target.Value
   End If


End Sub

Macro 2:

Private Sub Worksheet_Change(ByVal Target As Range)

Columns("A:B").Sort Key1:=Range("A1"), Header:=xlGuess, OrderCustom:=1, _
   MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
   
End Sub

I tried inserting Macro 2 in Macro 1 (specifying the correct worksheet), but I kept making some mistake that I couldn't find, so I made the two macros instead. Makes sense, really, since in some cases people might prefer to add types of activism (and notes) directly on the list sheet.

Okay, last question for now: I obviously copied a lot of the code from the example I was given. I think I understand most of the code, but there are bits I'm not clear on (e.g. ".End(xlUp)" or "Header:=xlGuess"). Can I find a useful concordance of the different functions, commands etc. anywhere on the internet? (Talking of which, I think I'll go and read all the VBA tips on MrExcel.com now! )

 

[RoryA]

The VBA help is actually not bad for some things (unless you're using 2007) and the Object Browser in the VBEditor (press f2) gives a searchable list of all the available objects, methods properties etc.
Other than that there are lots of useful websites like this one or cpearson.com etc. And a good VBA book is invaluable!