Combo box - only include column headers with certain data type

[spreadsheet411]

Hello all!

I have a form with various combo boxes that users can select. Currently I'm populating each combo box with every column header in my data set (not my data set is variable). Here's the code I'm using:

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
Me.cbAmount.AddItem sh.Cells(1, i).Value
Next i

I want to be able to have specific combo boxes only include those headers where the column contains dates, and others where the column only contains values etc.

How can I best read an entire column to determine the type of data in that column?

Very interested to see what type of creative ideas this group has!

 

[Akuini]

Hi, spreadsheet411. Welcome to the Forum.

You can check row 2 to see if it's a date or not.

For the combobox that lists the column with dates.

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
    If IsDate(sh.Cells(2, i).Value) Then
        Me.cbAmount.AddItem sh.Cells(1, i).Value
    End If
Next i

the other one:

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
    If Not IsDate(sh.Cells(2, i).Value) Then
        Me.cbAmount.AddItem sh.Cells(1, i).Value
    End If
Next i

 

[spreadsheet411]

Hi, spreadsheet411. Welcome to the Forum.

You can check row 2 to see if it's a date or not.

For the combobox that lists the column with dates.

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
    If IsDate(sh.Cells(2, i).Value) Then
        Me.cbAmount.AddItem sh.Cells(1, i).Value
    End If
Next i

the other one:

For i = 1 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
    If Not IsDate(sh.Cells(2, i).Value) Then
        Me.cbAmount.AddItem sh.Cells(1, i).Value
    End If
Next i

Thanks Akuini! This is good...I was hoping that there would be a creative way to do this for the whole column. But I could probably put a check in to ensure the column doesn't have any blanks. The issue I see happening here is that if I need the column to only have dates or values to show up in the combo box, by checking just a single cell leaves me open for columns that contain multiple data types.

 

[Akuini]

...I was hoping that there would be a creative way to do this for the whole column.
..for columns that contain multiple data types.

Because you have columns with multiple data types then I don't think you can check the whole column at once, you need to loop each cell.