+ Reply to Thread
Results 1 to 5 of 5

VBA Filter by Date and button text caption

Hybrid View

  1. #1
    Registered User
    Join Date
    11-11-2022
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    13

    VBA Filter by Date and button text caption

    I have code that ideally when clicked will filter by the name of the button and prompt a date range for the end user to choose a start date and the resulting "Results" tab will only have dates equal to or greater than it

    Sub zzzFilterData()
    Dim wsData As Worksheet
    Dim wsResults As Worksheet
    Dim strCaption As String
    Dim lastRow As Long
    Dim startDate As Date
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    'Prompt the user to enter a start date
    startDate = InputBox("Enter a start date (format: MM/DD/YYYY)")
    
    strCaption = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text 'Get the caption of the button that was clicked
    
    'Check if the results sheet exists and create it if it doesn't
    On Error Resume Next
    Set wsResults = ThisWorkbook.Worksheets("Results")
    On Error GoTo 0
    If wsResults Is Nothing Then
        Set wsResults = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        wsResults.name = "Results"
    End If
    
    'Clear any existing data on the results sheet
    wsResults.Cells.Clear
    
    'Filter the data based on the button caption and start date
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row 'Get the last row of data in column A
    wsData.Range("A1").AutoFilter Field:=1, Criteria1:=strCaption, Operator:=xlAnd, Criteria2:=">=" & startDate 'Filter the data in column A based on the button caption and start date
    
    'Copy the filtered data to the results sheet
    wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsResults.Range("A1")
    
    'Remove the filter
    wsData.Range("A1").AutoFilter
    
    'Select the results sheet
    wsResults.Activate
    End Sub
    the Results tab however only has the headers and then blank columns. I have tried changing the code to
    lastRow = wsData.Cells(wsData.Rows.Count, "G").End(xlUp).Row 
    wsData.Range("G1").AutoFilter Field:=7
    as that is where the date in the data is but the end product is the same. Does anyone have any insight?

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,156

    Re: VBA Filter by Date and button text caption

    Some small example with the data mentioned in your post, please ... ?

  3. #3
    Registered User
    Join Date
    11-11-2022
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA Filter by Date and button text caption

    So the buttons will have names ex. apples, oranges, pears, bananas and when you click the button the code above is the macro that is clicked. Before I tried to add date as another filter the code was this:

    Sub zzzFilterData()
        Dim wsData As Worksheet
        Dim wsResults As Worksheet
        Dim strCaption As String
        Dim lastRow As Long
        
        Set wsData = ThisWorkbook.Worksheets("Sheet1") 'Replace Sheet1 with the name of your first sheet
        
        strCaption = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text 'Get the caption of the button that was clicked
        
        'Check if the results sheet exists and create it if it doesn't
        On Error Resume Next
        Set wsResults = ThisWorkbook.Worksheets("Results")
        On Error GoTo 0
        If wsResults Is Nothing Then
            Set wsResults = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            wsResults.name = "Results"
        End If
        
        'Clear any existing data on the results sheet
        wsResults.Cells.Clear
        
        'Filter the data based on the button caption
        lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row 'Get the last row of data in column A
        wsData.Range("A1").AutoFilter Field:=1, Criteria1:=strCaption 'Filter the data in column A based on the button caption
        
        'Copy the filtered data to the results sheet
        wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsResults.Range("A1")
        
        'Remove the filter
        wsData.Range("A1").AutoFilter
        
        'Select the results sheet
        wsResults.Activate
    End Sub
    which put all the data I needed other a 3rd "Results" tab. Now however I need to also filter it by date which is in column G on the data tab.

  4. #4
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,156

    Re: VBA Filter by Date and button text caption

    Where are the buttons, on which sheet ... what is "this ActiveSheet" name for "strCaption = ActiveSheet.Shapes(Application.Caller)" ?
    What is the date format in "this column G" ?

    ps.: Filtering dates via "vba-AutoFilter" is not such a "nice/easy" operation - you must be aware that what is a date on your computer is not necessarily a date on other people's computers (outside the US)
    (hence the request for a data sample)

  5. #5
    Registered User
    Join Date
    11-11-2022
    Location
    New Jersey, United States
    MS-Off Ver
    2016
    Posts
    13

    Re: VBA Filter by Date and button text caption

    Attachment 823933 the buttons are on a 2nd tab titled Buttons. The date format is date MM/DD/YYYY these dates are tied to the rows they are in as they are dates of when a service was rendered. So if apple was pressed id want it to also only give me apples with dates of 09/09/2019 and after for example. Capture.PNG this is the msgbox that appears and no error code message appears but when i go onto the results tab it is just the headers from the data tab and then it is empty
    Last edited by tacitend; 03-30-2023 at 03:26 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-27-2017, 09:17 AM
  2. [SOLVED] Form - select button & retrieve button name or button's caption
    By lexusap in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2016, 12:28 AM
  3. [SOLVED] VBA Changing button.caption with the same Button.Name
    By mcmunoz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-27-2013, 04:58 AM
  4. [SOLVED] How to determine Frame Text and option button caption value
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 10-17-2012, 08:14 AM
  5. Get Command Button Caption on Click & Log to Text File
    By Nu2Java in forum Excel General
    Replies: 3
    Last Post: 09-27-2012, 03:20 AM
  6. [SOLVED] Is it possible to do a comm.button with scrolling caption /changing caption?
    By Lkivagten in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2012, 08:10 AM
  7. Change caption or text of group box and option button
    By narayansingh.t in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-03-2011, 06:27 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1