Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Amending list in Data Validation List (2000)

    Is there a quick way to add a new entry to a List box without keep having to search for those cells listed in the Validation List.
    see screenshot.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Amending list in Data Validation List (2000)

    Instead of a validation list, you could use a combobox and have code to add a new entry to the list

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Amending list in Data Validation List (2000)

    Thats great thanks for that.

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

    Re: Amending list in Data Validation List (2000)

    If this is the case, would you not need to create a combo box for every single line in the list??
    That might be a tedious task!!
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Amending list in Data Validation List (2000)

    No you would not have to (though you could if you wanted to <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    You could create just one and have it move as needed via a selection change event. <post:=537,206>post 537,206</post:> has a simple example of the idea. validation, code to add to the list would have to be added.

    Steve

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

    Re: Amending list in Data Validation List (2000)

    This is great....very nice tip!

    I was experimenting with it and came across a minor glitch. I noticed that if you select a range of white cells and include just one (or more) of the yellow cells, the combo activates in that white cell. Is there a way to fix this? Its a great example for me, as I am still learning to get to grips with the intersect function!

    Tx
    Regards,
    Rudi

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Amending list in Data Validation List (2000)

    How about this?

    <pre>Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cbo As ComboBox
    Dim rCell As Range
    Set cbo = Me.myComboBox
    Set rCell = Intersect(Target, Range("A1:A10"))
    If Not rCell Is Nothing Then
    If rCell.Count = 1 Then
    With cbo
    .Visible = True
    .LinkedCell = rCell.Address
    .ListFillRange = Me.Range("C1:C8").Address
    .Left = rCell.Left
    .Top = rCell.Top
    .Width = rCell.Width
    .Height = rCell.Height
    End With
    End If
    Else
    cbo.Visible = False
    End If
    Set rCell = Nothing
    End Sub</pre>


    This will first check for the intercept then test for the number of cells selected.

    Steve

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

    Re: Amending list in Data Validation List (2000)

    Cool! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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