Can Combobox range be variable?
 

I have a combobox in a form. The box will show the values in column A.
The problem is somethies there is 65000 values/rows and sometimes only
1000.

Right now I have the range set as A5:A65000 but it makes the list quiet
empty for small datafiles.

Is there a way I can have the combobox only display values of filled
cells? The combobox is in a form so I dont know how to enter data other
than the peoperties in the editor ..

Matt

Can Combobox range be variable?
 

another problem I have is that it selects data and times ... In the
list the values show up as dats and times but if you selct one it will
show as 38596.4767824074 until you click anywhere else. Then it turns
back into a data/time. Is there a way to prevent the 38596.4767824074
from showing at all?

Matt

Can Combobox range be variable?
 

One way is to just format the list the way you want:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ComboBox1
For Each myCell In myRng.Cells
.AddItem myCell.Text
'or
.AddItem Format(myCell.Value, "mm/dd/yyyy hh:mm:ss")
Next myCell
End With
End Sub


Matt wrote:

I have a combobox in a form. The box will show the values in column A.
The problem is somethies there is 65000 values/rows and sometimes only
1000.

Right now I have the range set as A5:A65000 but it makes the list quiet
empty for small datafiles.

Is there a way I can have the combobox only display values of filled
cells? The combobox is in a form so I dont know how to enter data other
than the peoperties in the editor ..

Matt

--

Dave Peterson

Can Combobox range be variable?
 

Can you use a validation (Tools: Validation: List) and then "ignore blanks"?

This would collapse the blanks but leave any non-blank values.


"Matt" wrote:

I have a combobox in a form. The box will show the values in column A.
The problem is somethies there is 65000 values/rows and sometimes only
1000.

Right now I have the range set as A5:A65000 but it makes the list quiet
empty for small datafiles.

Is there a way I can have the combobox only display values of filled
cells? The combobox is in a form so I dont know how to enter data other
than the peoperties in the editor ..

Matt