Results 1 to 15 of 15

Thread: display filter

  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    display filter

    Hi all,

    I want to ask how to popup the custom filter via vba. suppose i have in column B dates i need to filter dates between two dates i need that code popup the custom filter of dates between.

    Thanks]
    farrukh

  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
    I suspect Sendkeys may be the only way to do that. Why do you need to do it?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Actually i am writing a code that open the specific file on search then copy this file to main file from here i have done.
    After that i need to filter dates between dates on prompt in column b then filter column c which contains sting open, close and update. I need to split data on sheet open if the filter based on open and same will be with close and update sheets according to filter of dates and status?

    Thanks
    Farrukh

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi farrukh

    If you can give a small example of the before and after we may be able to help.

    zeddy

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you turn the macro recorder on while doing the task, you can see the code that it uses to set the filter. You can use an input box in code to have the user enter in teh dates and then set the filter. [MS MVP Debra Dagliesh also has some Autofilter code examples on her website at http://www.contextures.com/xlautofilter03.html.]

    Unfortunately Excel does not seem to store the autofilter list anywhere (or it not accessible to VBA). It seems to create the list on the fly. If you wanted to create the list, you would have to create it at runtime as well [the easiest way I have found to do it is to create a temporary pivot table on a new sheet from the column of data to get the sorted unique list. after the pivot is listed, you can work with that range of data or use it to populate something]

    If you need specific details, you will need to provide an example file as zeddy has suggested.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi,

    I have attached the before and after file i manage to write code till there


    Code:
    
    Sub filter()
    Dim customerBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim customerFilename As String
    Dim customerWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim targetSheet As Worksheet
    Dim sourceSheet As Worksheet
    On Error Resume Next
    Sheets("Call_data_Here").Select
    Selection.AutoFilter
    Set targetWorkbook = Application.ActiveWorkbook
    filter = "Text files (*.xls),*.xls"
    caption = "Please Select an Desired file "
    customerFilename = Application.GetOpenFilename(filter, , caption)
    Set customerWorkbook = Application.Workbooks.Open(customerFilename)  ' assume range is A1 - C10 in sheet1 ' copy data from customer to target workbook
    Set targetSheet = targetWorkbook.Worksheets(1)
    Set sourceSheet = customerWorkbook.Worksheets(1)
    targetSheet.Range("A1", "m100").Value = sourceSheet.Range("A1", "m100").Value  '
    customerWorkbook.Close
    If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("B1").AutoFilter
    End If
    
    End Sub
    Attached Files Attached Files

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Farrukh

    see attached file.
    I have tested this with Excel2010 and Excel2007.

    Click the button to display a Form which will allow you to choose your start and end dates.

    The code will clear any previous records on the sheets before extracting records between the specified dates.
    I have added a sheet [Parameters] to simplify the creation of a criteria range, used for the filter extract.

    You can adapt as required.

    Please let me know if you have any problems with this.

    zeddy
    Attached Files Attached Files

  8. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-02-22)

  9. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for your wonderful help and time. Zeddy Iam using office 2007 when i click to button getting an error message
    " Object or class does not support set of events" will you please look at it.

    Thank you


    Cheers
    farrukh

  10. #9
    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
    Do you have the Calendar control available on your system?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. The Following User Says Thank You to rory For This Useful Post:

    farrukh (2012-02-22)

  12. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Farrukh

    The Calendar Control is an ActiveX control (actually a file called
    mscal.ocx) supplied with Microsoft Office. It is normally installed with
    a standard installation of Microsoft Office Professional or Microsoft Access,
    but if you can't find it on the list you will need to get hold of a copy. If you
    are distributing your file, or planning to use it on more than one computer, you
    will also need to make sure that the host computer has the mscal.ocx file
    installed. You will find it on the CD that your copy of Microsoft Office
    Professional came on, or you can download a copy from the internet.

    Do a search on Excel Calendar control.

    zeddy

  13. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-02-22)

  14. #11
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi Zeddy and rory ,

    Yes i am missing MSCAL.OCX in my system . i have downloaded it which is attached and paste to c:\windows\system32 and go to run to register like regsvr32 mscal.ocx

    Thank you such a wonderful work and help always appreciate :-)

    Thank you


    Cheers
    farrukh
    Attached Files Attached Files

  15. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi farrukh

    Hopefully you will now be able to see the calendar controls used in my file sent previously.
    The reason I wanted to use the Calandar control is to avoid any difficulties working with VBA and dates.
    For example, depending on where you are in the world, a date like 7/9/2012 can mean 7th Sep or 9th July.
    It can depend on your Windows settings and cell formats etc. VBA generally assumes dates are in mm/dd/yy format.
    The calandar control avoids such issues.

    zeddy

  16. #13
    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
    FYI, when passing text dates to and from userforms, use CDate to convert to an actual date using the regional settings of the machine rather than US format.
    Regards,
    Rory

    Microsoft MVP - Excel

  17. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Rory

    I've had previous bother with reading dates in csv files (generated in countries which use different date formats). Does my head in sometimes. I had to check what the PC settings were, change it to what I needed, process the csv file containing dates, then put the setting back to what it was. Or something like that. It was a while ago.
    So I'll try and remember Cdate will convert according to your machine's regional settings then.

    zeddy

  18. #15
    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 opening a csv file in code, you need to use Workbooks.Opentext and specify the Local:=True argument.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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