Excel ComboBox/Autocomplete Question(s) 1

[baltman]

I'm trying to put an Excel template together for data entry.

Particular columns are to be reserved for specific text values. It seems like putting comboboxes into each cell with set values will have problems if the N gets too high.

One columns' values will have over 1,000 possibilities.

My question is, can I set a column (range) to have an auto complete property based off of a specfic list (which can exist on another sheet)?

Can anyone suggest another method for this? I thought about a single combobox per column frozen at the top, but how would users indicate to which cell the selection is to be entered? I don't want it to be a frustrating experience.

I'd appreciate code snippets since I'm not 'expert' with VBA for MS apps.

Thanks,
Brian

 

[SkipVought]

Hi,

Consider using Data/Validation List -- which is like a combobox.

ALternatively, create 1 ONE and only ONE combobox and position it using the top, left, width, height properties and make it visible ON SELECTING certain ranges. Based on the context of selection, you can also assign different properties to the ListFillRange.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

Here's an example of something that I am currently working on...

Private Sub cbxSelectionList_Click()
    On Error Resume Next
    With cbxSelectionList
        .TopLeftCell.Value = .Value
        .Visible = False
    End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > [ProductHeading].Row Then Exit Sub
    With cbxSelectionList
        .Visible = True
        .Top = Target.Top
        .Left = Target.Left
        .Width = Target.Width
        .Height = Target.Height
        Select Case Target.Address
            Case [ProductHeading].Address
                .ListFillRange = "ProductList"
            Case [ContractHeading].Address
                .ListFillRange = "ContractList"
            Case [SerialNbrFromHeading].Address, [SerialNbrThruHeading].Address
                .ListFillRange = "SerialNbrList"
            Case Else
                .Visible = False
        End Select
    End With
End Sub

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[baltman]

Thanks Skip,

I didn't know of the List feature.

It's close to what we need and we'll probably just throw the lists towards the bottom the the columns they're for so we get some auto complete functionality too. It's too bad that the list doesn't support it natively.

Brian