list box help please please please
Posted by Duane Kennerson on December 08, 2000 3:28 PM
I have two list boxes on one of my user forms. The two list boxes are used together
to create criteria for an autofilter sort that I do. There are 7 items listed in on list
box and three in the other. The code behind the scenes works perfect for 6 out of the seven
items in the first list box. There is one that won't do anything at all when I press the command
button to do the autofilter. I'm confused because I cut and paste all the code and just changed
some of the criteria for the autofilter for each item. I have looked at the code a million times
(which is only about 8 lines for each autofilter). Every other combination works between the two
list boxes except for when I choose that one item from list box 1. Have I confused anybody yet?
Any suggestions? Does anybody know what is going on? I'll send you the workbook if I have to.
Here is the code.(partial so you can see what is going on). The cmdSort is
pressed and depending on the selections in the list boxes, this macro is done.
Private Sub cmdSort_Click()
ElseIf lstDepartment = "Freeport Studio" And lstShift = "1" Then
fs1
ElseIf lstDepartment = "Freeport Studio" And lstShift = "2" Then
fs2
ElseIf lstDepartment = "Freeport Studio" And lstShift = "3" Then
fs3
ElseIf lstDepartment = "Gift Box" And lstShift = "1" Then
gb1
ElseIf lstDepartment = "Gift Box" And lstShift = "2" Then
gb2
ElseIf lstDepartment = "Gift Box" And lstShift = "3" Then
gb3
End If
End Sub
After the criteria is determined here is the actual macro that does the work.
I have included the fs3 (freeport studio 3rd shift) and the gb1 (gift box 1st shift)
so that you can see the code. The freeport studio one works perfect as well as the
five other sections of code. The gift box code does not work but you can see
it is the same. When I press the sort button on the "sort form" it does nothing
even though gift box and 1st shift are highlighted in the list box. It works with
every other department and shif except the gift box. I have tried moving the order
around everyplace I can think of but to no avail. Can you help? Do you need
more info? Thanks in advance.
Sub fs3()
frmSort.Hide
Sheets("Sort Sheet").Select
Range("a1").Select
Application.Goto Reference:="dakbase"
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="Freeport Studio"
Selection.AutoFilter Field:=5, Criteria1:="3"
frmPrint.Show
End Sub
Sub gb1()
frmSort.Hide
Sheets("Sort Sheet").Select
Range("a1").Select
Application.Goto Reference:="dakbase"
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="Gift Box"
Selection.AutoFilter Field:=5, Criteria1:="1"
frmPrint.Show
End Sub
Thanks in advance....