data validation drop down with control combo box function

smokenack

Active Member
Joined
Dec 12, 2003
Messages
351
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Data Validated cells can be copied/pasted same as any other.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Active Member
Joined
Dec 12, 2003
Messages
351
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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. :-
Code:
'=============================================================================
'- 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

Active Member
Joined
Dec 12, 2003
Messages
351
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,590
Messages
5,637,279
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top