UserForm Filter Combo Box List

W

webeinky

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("Databasesheet").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.
 
P

Patrick Molloy

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
 
W

webeinky

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

Patrick Molloy

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
 
W

webeinky

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
 
P

Patrick Molloy

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 said:
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
Click to expand...
 
W

webeinky

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").Range("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,
 
P

Patrick Molloy

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"
 
W

webeinky

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

webeinky

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,
 
W

webeinky

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert list to UPPER, lower & Proper cases. 21
Copy contents to a selected sheet in another workbook 2
Macro copy data into another workbook 4
Write data in Last Row-column "B" 2
Export specific cell values to an external workbook 1
Copy a specific range of cell values into an external workbook 0
Macro copy values to a different workbook 4
Suppressing a screen 4

Top