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

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

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 -