Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm running an autofilter query with the following code:

    Sheets ("DCIData").Select
    Range ("E6").Select
    Selection.Autofilter
    Selection.Autofilter Field: = 5, Criterial: = lstText

    I'm getting an errof on the range statement. I think the problem may be tied to the fact that column E is setup as a list, so that when you click on any filled cell you get a little drop down arrow like you would get with a list box, except nothing appears in the box but that particular row. I believe I have to have the cursor in the correct row in order to run the autofilter, and I can do this manually, it's just not working programatically.

    Any help will be appreciated.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you are getting an error on the Range statement, I would suspect your code is in a worksheet code module, and that sheet is not the DCIData sheet. Try something like:
    Code:
    With Sheets ("DCIData").Range ("E6")
    .Autofilter
    .Autofilter Field: = 5, Criterial: = lstText
    End with
    Regards,
    Rory

    Microsoft MVP - Excel

  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='rory' post='797098' date='09-Oct-2009 09:20']If you are getting an error on the Range statement, I would suspect your code is in a worksheet code module, and that sheet is not the DCIData sheet. Try something like:
    Code:
    With Sheets ("DCIData").Range ("E6")
    .Autofilter
    .Autofilter Field: = 5, Criterial: = lstText
    End with
    [/quote]
    Thanks, but that doesn't work any better than Range("$E$6").Select.
    I'm working from a code module in another workbook, but I've also tried the same code from another tab in the same workbook and it didn't work either. I think there's something about the entire range E6-E211 being in a List that's preventing me from selecting a one cell range, but I don't know exactly what is causing the problem.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is it just column E that is set up as a list? Is that the only list on the sheet?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='798071' date='15-Oct-2009 03:11']Is it just column E that is set up as a list? Is that the only list on the sheet?[/quote]
    Columns, E, F and G are setup as lists.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [size="4"][quote name='merlynsdad' post='798280' date='15-Oct-2009 20:48']Columns, E, F and G are setup as lists.[/quote]
    One other thing, when the names to be run thru the autofilter are selected in the listbox, they tend to hang. The correct name gets sent to autofilter, but the correct name isn't necessarily the one highlighted in the listbox. I have application.screenupdating set to false, but even when I turn it on to check the selection showing in the Listbox isn't necessarily the name actually selected. If I horizontally scroll the listbox off the screen and back, it comes up with the correct name selected, but I don't want to tell people to do that.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Are they separate lists? (and if so, why?)
    Any chance you could post a suitably censored workbook so we can see exactly what is where?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='798338' date='16-Oct-2009 07:08']Are they separate lists? (and if so, why?)
    Any chance you could post a suitably censored workbook so we can see exactly what is where?[/quote]
    I'll try to post parts of both the query workbook and the master spreadsheetit tonight. I can't post to this site from work. Thanks.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='798338' date='16-Oct-2009 07:08']Are they separate lists? (and if so, why?)
    Any chance you could post a suitably censored workbook so we can see exactly what is where?[/quote]
    I'm attaching two Excel 2003 workbooks, real1.xls and mgrquery.xls. The idea is that in mgrquery one can select an employee, team or date range and run a query from the DCIdata tab in real1.xls without ever having to see real1.xls. The code is attached to the listbox controls, and all are similar, so I'm working out the kinks with Query by Employee for now. One other problem that popped up yesterday is that while designing this, real1.xls has always been opened before mgrquery.xls. Since that's probably not going to be the case, I need to open real1 from the code, and then refer to it from mgrquery, since I go back and forth between the two workbooks cutting and pasting. I tried that yesterday using Application.Workbooks.Open (cstrDatabaseWB) and it opened real1.xls but none of the listboxes in mgrquery.xls populated, even though the links are set to automatically update. Bear in mind that I sanitized these and uploaded them at home in Excel 2007, but am working in 2003 at work. I haven't worked with Excel code in a few years and am very rusty. Any help will be greatly appreciated. Thanks in advance.
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The workbook real1.xls has been saved while E106 is the active cell on the DCIData sheet. This is outside the data table, so AutoFilter can't be activated.

    You can't use Range("E6").Select because the code is in a worksheet module of mrquery.xls. You can use

    Sheets("DCIData").Range("E6").Select

    or even better, don't select at all, but apply AutoFilter to the range.

  11. #11
    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='798565' date='17-Oct-2009 12:42']The workbook real1.xls has been saved while E106 is the active cell on the DCIData sheet. This is outside the data table, so AutoFilter can't be activated.

    You can't use Range("E6").Select because the code is in a worksheet module of mrquery.xls. You can use

    Sheets("DCIData").Range("E6").Select

    or even better, don't select at all, but apply AutoFilter to the range.[/quote]
    Sorry, I'll resave and resend. There are actually over 1600 items in column E but I sanitized it down to 100 or so.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    ...
    		With Workbooks(cstrDatabaseWB).Sheets("DCIData").Range("$e$6")
    		  .AutoFilter field:=5, Criteria1:=lstText
    		  '   Copy the query results to the Query tab in this workbook
    		  .CurrentRegion.Copy
    		End With
    ...
    You may want to clear the contents of the rows below row 16 in QueryEmp before copying/pasting the data:

    Range("A17:A" & Rows.Count).EntireRow.ClearContents

  13. #13
    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='798590' date='17-Oct-2009 16:53']Try this:

    Code:
    ...
    		With Workbooks(cstrDatabaseWB).Sheets("DCIData").Range("$e$6")
    		  .AutoFilter field:=5, Criteria1:=lstText
    		  '   Copy the query results to the Query tab in this workbook
    		  .CurrentRegion.Copy
    		End With
    ...
    You may want to clear the contents of the rows below row 16 in QueryEmp before copying/pasting the data:

    Range("A17:A" & Rows.Count).EntireRow.ClearContents[/quote]
    Thanks, clearing them with code is better than relying on the user to clear them before doing another query.

    Any advice on the listboxes not populating (see post above) when mgrquery is opened before real1?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use code like this in the Workbook_Open event in the ThisWorkbook module of mrquery.xls. I only added a line for the list box on QryEmp, you'll have to add the rest yourself:

    Code:
    Private Sub Workbook_Open()
      ' Open the real1.xls workbook
      Workbooks.Open "real1.xls"
      ' Activate mrquery.xls again
      Me.Activate
      ' Update list box
      Worksheets("QueryEmp").lstEmployee.ListFillRange = "[real1.xls]lists!C2:C211"
      ' Add a line for each list box
    End Sub

  15. #15
    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='798651' date='18-Oct-2009 11:43']You could use code like this in the Workbook_Open event in the ThisWorkbook module of mrquery.xls. I only added a line for the list box on QryEmp, you'll have to add the rest yourself:

    Code:
    Private Sub Workbook_Open()
      ' Open the real1.xls workbook
      Workbooks.Open "real1.xls"
      ' Activate mrquery.xls again
      Me.Activate
      ' Update list box
      Worksheets("QueryEmp").lstEmployee.ListFillRange = "[real1.xls]lists!C2:C211"
      ' Add a line for each list box
    End Sub
    [/quote]

    This worked perfectly! Thanks! When I switch this to a network drive, using Const = cstrDatabaseWB as String = "g:\whatever.myfile.xls" what's the syntax for the constant that replaces [real1.xls]? I've tried it several ways (cstrDatabaseWB), ("cstrDatabaseWB), [cstrDatabaseWB] etc. without success.

    Also, going back up a few posts, the

    With Workbooks (cstrDatabaseWB), Sheets ("DCIdata").Range("$e$6")
    .Autofilter field:=5, Criteria1:=lstText
    End with

    This didn't work. I'm still trying to figure out how to activate the other workbook (which is opened at this point) on a network drive since Windows("filename").Activate apparently only works on the filename, not the entire path.

    Thanks. I need to take a course but don't have the time!

Page 1 of 2 12 LastLast

Posting Permissions

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