data validation drop down with control combo box function

[smokenack]

I have a sheet that uses the data validation drop down boxes. A user has asked me if I can incorporate an autofill feature. As far as I know this isn't available with data validation but only with a control combo box. For me control combo boxes are a complete pain in the backside. I really don't want to have to copy a box 150 times and adjust the linked cell in each one.

Any way to do this in data validation or is there a way for combo boxes to adjust the linked cell as they are being pasted?

thanks for looking

Nick

 

[BrianB]

Data Validated cells can be copied/pasted same as any other.

 

[Jonmo1]

I think what smokenack is asking is if a data validation list can "AutoComplete" when you start typing.

Like if your data validation list contains names

Jon
George
Eric
Fred
Barney

And you type an E - it will auto populate with Eric.


sorry, smokenack, I don't know the answer either. I was just trying to clarify the question (hope I understood it right). But I'd sure like to know as well.

 

[smokenack]

Yes jonmo, that's exactly it.

Another problem is with a combo box linked to a data validated cell. The data validation doesn't work when the cell value is the result of an entry in the combo box. I had considered situating a combo box above every data validated cell and then spending an hour or two linking them. Not worth the bother if the data validation doesn't work.

 

[BrianB]

Hi guys. Seems I didn't fully understand the question. (thanks jonmo1) Sorry about that.

First off I will say that I generally avoid using controls from the Controls toolbox if I have to use many on a worksheet. They are really designed for userforms I suppose. More features, more bugs. What happens is that things seem to work for a time, then all sorts of things go haywire as the project development progresses - to the state where the workbook becomes corrupted and refuses to open - so be warned. However, there seems to be no other way here - and at least the controls are made anew each time.

Setting this up was no exception to the above. I kept getting a message "Object doesn't support this property or method". Searching the web, it seems this is a common occurrence. Trapping the errror with "On Error Resume Next" strangely seems to have resolved the problem.

Here is the basic code then. I would be interested to know how things go after it has been used for a time. :-

'=============================================================================
'- ADD SEVERAL COMBOBOXES TO A WORKSHEET TO SIMULATE DATA VALIDATION
'- NB. used 'On Error Resume Next' to stop message
'-     "Object doesn't support this property or method"
'-     but this does not seem to affect anything.
'- Brian Baulsom July 2007 using Excel 2000
'==============================================================================
Sub ADD_COMBOBOXES()
    Dim ToRow As Long
    Dim MyCell As Range
    '--------------------------------------------------------------------------
    '- worksheet
    With ActiveSheet.Cells
        .Font.Name = "Arial"
        .Font.Size = 8
        .Columns(1).ColumnWidth = 20
        .Rows.RowHeight = 15
    End With
    '---------------------------------------------------------------------------
    '- TO TRAP UNEXPLAINED ERROR MSG
    On Error Resume Next
    '---------------------------------------------------------------------------
    '- Add ComboBoxes
    For ToRow = 2 To 10
        Set MyCell = ActiveSheet.Cells(ToRow, 1)
        '- Add Combobox
         With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
            .Name = "CB" & CStr(ToRow)
            .ListFillRange = "$J$1:$J$10"
            .Placement = xlMoveAndSize
            .LinkedCell = MyCell.Address
            .Left = MyCell.Left
            .Top = MyCell.Top
            .Width = MyCell.Width
            .Height = MyCell.Height
            .MatchRequired = True
            .ListRows = 10
        End With
    Next
    '---------------------------------------------------------------------------
    MsgBox ("Done")

End Sub
'--------------------------------------------------------------------------------

 

[smokenack]

Thanks Brian, I'll let you know in a while how it goes then. Any ideas on why the data validation won't work?

Nick