If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Validation Lists
Thanks for the response. I am very new to all of the coding. I have some more questions related to my first posting. I have a "Lists" spreadsheet that has a number of named ranges in it. My first named range "ReferringReason" pulls correctly into my drop-down list and I am able to free-text other criteria in the cells that will then add to my drop-down list. However, I have other named ranges that I need to pull into other drop-down lists. How do I replicate the code so that it will work? I assume that I need to change the Target Column and the Named Range (ex. ReferringProvider) . I keep getting error messages. On my January spreadsheet, I have the following codes set up: Option Explicit 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 = 5 And Target.Row 1 Then If Application.WorksheetFunction.CountIf(ws.Range("Re ferringReason"), 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("ReferringReason").Sort Key1:=ws.Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If End If End Sub Thank you so much!! "Gord Dibben" wrote: Download a sample workbook from Debra Dalgleish's site. http://www.contextures.on.ca/excelfiles.html#DataVal Scroll down to DV0012 and download the workbook. DV0012 - Update Validation List -- type a new value in a cell that contains data validation, and it's automatically added to the source list, and the list is sorted; a macro automates the list updates. Gord Dibben MS Excel MVP On Mon, 8 Feb 2010 15:05:01 -0800, Sue wrote: I have a question regarding the drop downs lists created using Data Validation. Is there any way to make them so that when I type something new into the box that it is automatically added to the drop down list? Thank you!! |
Thread Tools | |
Display Modes | |
|
|