Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Automatic entery into a Validation List (Excel 2003)

    Here is a method to add a value into a validation list by simply typing the entry into the cell with the validation arrow. Normally you would have to open the validation dialog and add an enrty manually into the source for that list, but using this method all you need to do is type the value into the cell and it adds into the list automatically.

    Follow these steps:

    1. Add any list of names or items to the range A1:A10 on any sheet.
    2. Now in cell A11 enter this formula and copy it down to say row 20. =IF(COUNTIF($A$1:A10,$D$1),"x",$D$1) Note the relative reference of A10
    3. Go to Insert>Name-define and in the Names in workbook: box type: MyNames
    4. In the Refers to: box enter this formula: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A:$A,"<>x" ),1)
    5. Click Add then Ok.
    6. Select Cell D1 and go to Data>Validation, select List from the Allow: box and in the Source: box type: =MyNames Ensure the In-cell dropdown box is checked.
    7. Click the Error Alert page tab and uncheck the Show error alert after invalid data is entered box. Now click Ok.
    8. Right click on the sheet name tab and select View Code in here paste the code below:

    Private Sub Worksheet_Calculate()
    On Error Resume Next
    Application.EnableEvents = False
    Range("MyNames") = Range("MyNames").Value
    Application.EnableEvents = True
    On Error GoTo 0
    End Sub

    9. Click the top right X to get back to Excel and now Save.

    PS: I found this info on the web some while back and thought it worth a post. I managed to find the original site where I got the info... You can find them here:
    Excel Data Validation List - Automatically Add to a Data Validation List. I see the content on their site has changed based on this detail...it is probably due to version updates...
    Regards,
    Rudi

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic entery into a Validation List (Excel 2003)

    Thanks Rudi

    I will go and play with that

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Automatic entery into a Validation List (Excel

    I'm just copying the code snippet from your link
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lReply As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$D$1" Then
    If IsEmpty(Target) Then Exit Sub
    If WorksheetFunction.CountIf(Range("MyNames"), Target) = 0 Then
    lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
    If lReply = vbYes Then
    Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target
    End If
    End If
    End If
    End Sub
    </pre>



    I think I can make this work for me - but I've never used the Worksheet Change event before - is it specific to the particular sheet being clicked on?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatic entery into a Validation List (Excel

    Yes, the Worksheet_Change event procedure is specific to one worksheet - the sheet in whose code module the event procedure is stored. You open a sheet's code module by right-clicking the sheet tab and selecting View Code from the popup menu.

    It is also possible to write code that will work for all sheets in a workbook, by using the Workbook_SheetChange event procedure in the ThisWorkbook module of the workbook.

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Automatic entery into a Validation List (Excel

    Thanks Hans
    I prefer to keep my validation list sources on a separate sheet - so the Workbook_SheetChange event works better for me.

    So in case anyone else is interested. The following adds items to the source for the validation list. If the user doesn't agree to
    update the list, the entry is deleted.

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim strMessage As String
    Dim strTitle As String
    Dim lReply As Long

    ' update Chemicals list
    If Target.Cells.Count > 1 Then Exit Sub 'if more than one cell is selected - quit
    If Target.Column = 1 Then 'if in Chemicals column (Column A)
    If IsEmpty(Target) Then Exit Sub 'if cell is empty - quit
    'if the item isn't on the list
    If WorksheetFunction.CountIf(Range("Chemicals"), Target) = 0 Then
    strTitle = "Chemical Inventory"
    strMessage = "Do you want to Add " & Target & " to the list of Chemicals?" & _
    " If " & Target & " is not added, it will be deleted. "
    lReply = MsgBox(strMessage, vbYesNo + vbQuestion, strTitle) 'ask the user
    If lReply = vbYes Then 'if yes add to list, if no delete
    Range("Chemicals").Cells(Range("Chemicals").Rows.C ount, 1) = Target
    'sort the updated list
    Range("ChemCas").Sort Key1:=Range("Chemicals"), Order1:=xlAscending, Header _
    :=xlNo, MatchCase:=False, Orientation:=xlTopToBottom _
    , DataOption1:=xlSortTextAsNumbers
    Else
    Target.Value = ""
    End If
    End If
    End If
    End Sub

    </pre>

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •