glad you figured it out - you'll be a guru very soon now ;)

"webeinky" wrote in message
...
Patrick,

Oh Happy Days!!! I've got it figured out:

Me.cmbOrderNumber.List(Me.cmbOrderNumber.ListCount - 1, 1) =
.Cells(cell.Row, "A").Row

Does the trick. I have even got my "Find" button to work with pulling
through the correct row number.

Thanks so much for your help, I won't have been able to do this without
you.

Nancy


"webeinky" wrote:

Patrick,

I have done some more "playing" around and have gotten this code to work
to
show what is colum "B":

Dim cell As Range
With bk.Worksheets("Database")
For Each cell In .Range("OrderCompleted").Cells
If cell.Value = "" Then
Me.cmbOrderNumber.AddItem .Cells(cell.Row, "A")
Me.cmbOrderNumber.List(Me.cmbOrderNumber.ListCount - 1, 1) =
.Cells(cell.Row, "B")
End If
Next
End With



What is the code to capture the Row number? I know I would put in
instead
of ".Cells(cell.Row, "B")" in the code.

Thanks,

--
Nancy


"webeinky" wrote:

Sorry I'm lost once again. I don't completely get the sample you gave
me.
Could you please show me exactly how this part of my code should be to
get
the 2nd column to be the row number (this of course is from the
"Private Sub
UserForm_Initialize()" coding) :


Dim cell As Range
With bk.Worksheets("Database")
For Each cell In .Range("OrderCompleted").Cells
If cell.Value = "" Then
Me.cmbOrderNumber.AddItem .Cells(cell.Row, "A")
End If
Next
End With


I have changed the settings like you said, but I'm not sure what should
be
in place of your "A" and "Acol1" and "B" and "Bcol1" in your example
and I'm
not sure what code to put to get the row number in the second column of
my
list.

Once again Thanks.
--
Nancy


"Patrick Molloy" wrote:

ah. you can't as it is now but the fix is easy enough
set the list box columncount property to 2 and set the boundcolumn to
two as
well
we'll use column 1 for the order number and we'll now save the row
into the
second column

by setting the bound column to 2, the value returned by the click
event is
from the bound coulumn, ie the row number, however we don't need to
do it
that way

adding items means just another line
we ad the first column item as normal, that increments the listcount,
and we
use that to add another coulumn

.AddItem "A"
.List(.ListCount - 1, 1) = "Acol1"
.AddItem "B"
.List(.ListCount - 1, 1) = "Bcol1"




"webeinky" wrote in message
...
Patrick,

That did the trick. Awesome. Thank you very much for your help
and
patience answering my questions.

Hope you did well today playing golf.

Do you mind helping me take this a step futher, now that that
works.

Once the Order Number is selected the user clicks on a button to
find the
record. I need to change the coding so it uses the row number of
the
selection instead of the ListIndex and I'm not sure how to do that.

Here is the current coding:

Private Sub cmdFindRecord_Click()

Dim RowRange As Range
Set RowRange =
Workbooks("Database.xls").Worksheets("Database").R ange("a:a").Rows
_
(Me.cmbOrderNumber.ListIndex + 2)

If Me.cmbOrderNumber.ListIndex < -1 Then
With frmFindOrderForm
.TxtDate.Value = RowRange.Columns(2).Value
.TxtClosureType.Value = RowRange.Columns(3).Value
.TxtClosureSize.Value = RowRange.Columns(4).Value
.TxtLabelType.Value = RowRange.Columns(5).Value
.ChkBxFace.Value = RowRange.Columns(6).Value
.ChkbxBack.Value = RowRange.Columns(7).Value
.ChkbxNeck.Value = RowRange.Columns(8).Value
.ChkbxSpecial.Value = RowRange.Columns(9).Value
.TxtSpecialInfo.Value = RowRange.Columns(10).Value
.TxtOrderReviewed.Value = RowRange.Columns(11).Value
.TxtBottlingBegin.Value = RowRange.Columns(12).Value
.TxtOrderCompleted.Value = RowRange.Columns(13).Value


End With
End If

End Sub


Thanks,
--
Nancy


"Patrick Molloy" wrote:

oops. me bad
and sorry for tardy response - today' a golf day :)


yes rw is row number BUT it was coded wrong, here's the correct
line
cmbOrderNumber.AddItem .Cells(cell.row,"A")

so the loop checks the order completed column, and where the IF
condition
is
true, the order mumber is added to the combo. the order number
will be in
the same row as the completed check, so you just need to replace
the
column
letter - I used A in my test

"webeinky" wrote in message
...
Patrick, Once again thank you. I am sorry for being so dense
with
this.

I am getting closer. :-) But I am still getting the same error
as
noted
before.

I believe it is with the following line:
cmbOrderNumber.AddItem .Cells(Rw, "A")

I have done some testing and if I use cmbOrderNumber.AddItem
.Cells(,
1)
I
get a list of Row 1's First Column Data - which is what I
expected.

If I use I use cmbOrderNumber.AddItem .Cells(3, 1) I get a list
of
Row3's
First Column Data - which is what I expected.

So my question is: Is "Rw" suppose to be identifying the row?
Am I
missing
some code to make "Rw" the number of the row? What would that
code be?
Where would I put it?

Thanks again
--
Nancy


"Patrick Molloy" wrote:

you need to change this

For Each cell In Range("OrderCompleted").Cells

to

WITH bk.worksheets("????")
For Each cell in .Range("OrderCompleted").Cells 'NOTE . before
Range(
If cell.Value = "40041" Then
cmbOrderNumber.AddItem .Cells(Rw, "A") ' NOTE .before Cells(
End If
Next
end with



"webeinky" wrote in
message
...
Hi Patrick,

Thank you for your quick reply. I have tried to incorporate
your
suggestion
but the But the "Then" part of the statement is erroring out.
What
I
have
is:

Private Sub UserForm_Initialize()

Dim bk As Workbook

' test to see if Destination.xls is already open
On Error Resume Next
Set bk = Workbooks("Database.xls")
Windows("Database.xls").Visible = False
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:\My Documents\Database.xls")
Windows("Database.xls").Visible = False
End If

Dim cell As Range
For Each cell In Range("OrderCompleted").Cells
If cell.Value = "40041" Then
cmbOrderNumber.AddItem Cells(Rw, "A")
End If
Next

End Sub


Before I start trying to make the date work I thought I'd
test with
data
that is why I have the cell.value = "40041" I know that is
one of
the
values
in "OrderCompleted".

Due to testing, I know I am erroring on the "Then" part of
the
statement.
The error I receive is Run-time error "1004'
Application-defined or
object-defined error.

Can you tell where I have gone wrong? Please keep in mind
that the
ComboBox
is in a different workbook from the Database

Thanks very much for your help.


--
Nancy


"Patrick Molloy" wrote:

maybe using the form's initialization to populate

Private Sub UserForm_Initialize()
Dim cell As Range
For Each cell In Range("OrderCompleted").Cells
If cell = "" Or (CDate(cell.Value) - Date) =
3 Then
ComboBox1.AddItem Cells(rw, "B")
End If
Next
End Sub

i assume that your Order Numbers are in column B ... please
change
as
appropriate

"webeinky" wrote in
message
...
I have created a UserForm to retrieve information from
another
workbook
for
the user to view based on the Order Number they choose
from a
ComboBox
list.

The following code works great for listing ALL the order
numbers
in
the
ComboBox. However I would like to limit the list to show
just
the
not
Completed orders and completed in the past 3 days. There
is a
column
in
the
Database where a completed date is entered, and I have
Defined
the
name
as
€œOrderCompleted€�.

Can someone help me put the correct coding in that would
be for
If the OrderCompleted cell is Null (or blank) or 3 days
ago then
show
the