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

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....

Combobox List to list Numerical Values ONLY in Column A is specifi
 

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

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Corey" wrote:

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....

Combobox List to list Numerical Values ONLY in Column A is specifi
 

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....


"John Bundy" remove X''''''''''''''''s wrote in
message ...
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

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Corey" wrote:

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....

Combobox List to list Numerical Values ONLY in Column A is spe
 

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Corey" wrote:

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....


"John Bundy" remove X''''''''''''''''s wrote in
message ...
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

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Corey" wrote:

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....

Combobox List to list Numerical Values ONLY in Column A is spe
 

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....
"Martin Fishlock" wrote in message
...
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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Corey" wrote:

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....


"John Bundy" remove X''''''''''''''''s wrote in
message ...
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

--
-John Northwest11
Please rate when your question is answered to help us and others know
what
is helpful.


"Corey" wrote:

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....

Combobox List to list Numerical Values ONLY in Column A is spe
 

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?
(nospam)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Corey" wrote:

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....
"Martin Fishlock" wrote in message
...
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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Corey" wrote:

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....


"John Bundy" remove X''''''''''''''''s wrote in
message ...
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

--
-John Northwest11
Please rate when your question is answered to help us and others know
what
is helpful.


"Corey" wrote:

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....