Combobox List to list Numerical Values ONLY in Column A is specific Sheet....

[Corey]

I am setting up a userform and have placed a Combobox on it.
I want it to list Numerical values ONLY from a sheet named "Data Sheet for
Inspections", in Colum "A".

As data is input the Column and over time, there will only be a text value
of "Roll#" AND an Numerial Value Custom Formatted as "#123". IE. Hash in
front of the value.

There will be a 3 digit numerical value every 25 rows or so, and a text
value of "Roll#" above it,
ALL other cells in Column "A" will be left blank.

So, is ther a way to fill the combobox with ONLY the numerical values in
this sheet in column A, EXCLUDING the Text AND EMPTY Cells ?

How ?

Corey....

 

[Guest]

See if this does what you are asking, on the userformactivate paste this, it
will add all numbers in column A and thats it.

Private Sub UserForm_Activate()
Dim lastrow As Integer
Dim myRow As Integer

lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 2

Do Until myRow = lastcell

If IsNumeric(Cells(myRow, 1)) = True And Cells(myRow, 1) <> "" Then
ComboBox1.AddItem (Cells(myRow, 1))
myRow = myRow + 1
Loop
End Sub

 

[Corey]

Thank you for the reply John.
I placed the below code in the required section as:

Private Sub UserForm_Activate()
Dim lastrow As Integer
Dim myRow As Integer
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 2
Do Until myRow = lastcell
If IsNumeric(Cells(myRow, 1)) = True And Cells(myRow, 1) <> "" Then
ComboBox1.AddItem (Cells(myRow, 1))
myRow = myRow + 1
End If
Loop
End Sub

But the Userform is White with no text and Freezes up Excel. Must Exit
Excelt to get rid of White Userform ??

Corey....

 

[Guest]

Corey,

Try using the initialize event instead of the activate event.

Please not that you should use longs for rows and (now columns) rather than
integers.

I changed the loop to a for loop as the do until was not picking up the last
row.

You can will use the do utill but you should change it to > rather than =.

Delete the other sub and then paste this in.

Private Sub UserForm_Initialize()

Dim lastcell As Long
Dim myRow As Long

lastcell = Cells(Rows.Count, "A").End(xlUp).Row

With ActiveWorkbook.Worksheets("Data Sheet for Inspections")
For myRow = 2 To lastcell
If .Cells(myRow, 1) <> "" Then
If IsNumeric(.Cells(myRow, 1)) = True Then
Me.ComboBox1.AddItem .Cells(myRow, 1)
End If
End If

Next myRow
End With

End Sub

 

[Corey]

Thank you for your post Martin.

I have pasted what you posted and replaced the previous code.

I do not seem to get any values displying in the combobox though.

I checked the combobox number is right(combobox1)

I even removed ALL data from row A and left a lonely 500 value in A2, with
still Nothing in the combobox list??


Any idea's?

Corey....

 

[Guest]

Thanks Marting for the reminder on the longs.
Corey, is there anyway you can e-mail me that workbook so I can see what
your doing and how?
(e-mail address removed)(nospam)