Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrow

    I have a workbook (Mgrquery.xls) with 3 tabs, QueryEmp, QueryTeam and QueryDCI. Each of these tabs has a listbox where one can select the Employee, or the Team, or the DCI (employee number). The code for running these three list boxes is virtually the same, in that they all run an autofilter on another worksheet (real1.xls), then copy the results of the query back to the respective tab on Mgrquery.xls. The code for that is as follows:

    Private Sub lstDCI (or lstTeam, or lstEmployee) ()

    Dim ListIndex As Long
    Dim list As String
    Dim lstText As String
    Dim myRange As Range
    Dim i As Integer

    ' On Error GoTo HandleError

    ' Select the employee
    Application.ScreenUpdating = False
    lstText = lstDCI.Text

    ' Run the DCIData query on the employee from the main workbook
    Windows("real1.xls").Activate
    Sheets("DCIData").Select
    Set myRange = Range("e7")
    Selection.AutoFilter
    Selection.AutoFilter field:=4, Criteria1:=lstText

    ' Copy the query results to the Query tab in this workbook

    ActiveSheet.Cells.Select
    Selection.Copy
    ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
    Windows("mgrquery.xls").Activate
    ActiveWorkbook.Sheets("QueryEmp").Select
    Range("a16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False


    Earlier today all of the code was working. Now all of the Subs have started doing something weird. After they execute the Selection.Autofilter field:=4, Criteria1: = 1stText code they all jump to the top of the lstDCI sub. DCIData actually runs the autofilter command, but I cannot figure out why the code jumps from the other code modules to the lstDCI sub in mid-code. If I comment out the entire lstDCI module the others work just fine. Iíve never seen anything quite like this before. HELP!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is the ListFillRange of the list boxes the autofiltered list? If so, applying autofilter will cause the On Click event of the list box to fire...

    Note: it would be nice if you provided some feedback to the replies that you receive. You haven't reacted to the last reply in several of your recent threads. That way, neither the person who replied nor other Loungers reading it know whether the reply was helpful. Thanks in advance!

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798035' date='14-Oct-2009 19:15']Is the ListFillRange of the list boxes the autofiltered list? If so, applying autofilter will cause the On Click event of the list box to fire...

    Note: it would be nice if you provided some feedback to the replies that you receive. You haven't reacted to the last reply in several of your recent threads. That way, neither the person who replied nor other Loungers reading it know whether the reply was helpful. Thanks in advance![/quote]
    Oh sorry, the last couple of answers were extremely helpful. I'm not able to post on this site at work due to the security settings on the computers (I work for the government), so I end up posting the questions from home at night, and then trying out the answers at work the next day. That creates a bit of a disconnect between when I get the answer and when I try out the suggestion. But I'm very appreciative of all the help you and the Loungers are giving me. Thanks much.

    I won't get a chance to try out this suggestion until tomorrow, but I'll let you know how this worked out.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798035' date='14-Oct-2009 19:15']Is the ListFillRange of the list boxes the autofiltered list? If so, applying autofilter will cause the On Click event of the list box to fire...

    Note: it would be nice if you provided some feedback to the replies that you receive. You haven't reacted to the last reply in several of your recent threads. That way, neither the person who replied nor other Loungers reading it know whether the reply was helpful. Thanks in advance![/quote]
    Yeah, actually, now that I checked it out the ListFillRange in that query is the autofiltered list. It's the only one that references the autofiltered list, and that's the one causing the problem. We solved the problem the easy way though, by removing that query tab from the workbook. Thanks for the info though!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •