Amending list in Data Validation List (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Steve

Reply | Quote

Thats great thanks for that.

Reply | Quote

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!!

Reply | Quote

No you would not have to (though you could if you wanted to .

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

Steve

Reply | Quote

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

Reply | Quote

How about this?

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

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

Steve

Reply | Quote

Cool!

Reply | Quote