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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from
reading
these posts.

--
Nancy

Nancy

Where are you giving Rw a value?


Try adding this.

rw = cell.Row

On Jun 22, 10:04*pm, webeinky
wrote:
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
OrderNumber in the ComboBox list.

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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").*Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub

I thank you for all your help. *I have learned a great deal from reading
these posts.

--
Nancy- Hide quoted text -

- Show quoted text -

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from
reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from
reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from
reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from
reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr
End Sub



I thank you for all your help. I have learned a great deal from
reading
these posts.

--
Nancy

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
OrderNumber in the ComboBox list.


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 myArr As Variant

'create the list
myArr =
Workbooks("Database.xls").Worksheets("Databaseshee t").Range("OrderNumber").Value

'assign the array directly to the list.
Me.cmbOrderNumber.List = myArr

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

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