+ Reply to Thread
Results 1 to 2 of 2

Filtering with Custom made Combo Box

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-02-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    353

    Filtering with Custom made Combo Box

    Hi all,
    I would like your help in a report that I am working on.
    It's quite complicated to me and I hope you can take some time to help me.
    I'm desperate!

    I have 2 sheets, actually a lot but I didn't include on the dummy Excel sheet as it is not relevant.

    On my Sheet1 (Sales Dashboard) contains combo boxes and a Table.
    On my Sheet2 (DataSource) is the value lookup.

    What I'd like to do is to create some sort of filter.
    When clicking "Run Query" button I would like to populate my table
    (starting from C42 of Sales Dashboard Sheet) based on the choices of my Combo box.

    I'm so desperate and I really really need your help.
    Thank you so very much in advance.

    PS
    I have attached my dummy worksheet.
    Attached Files Attached Files
    Last edited by Andrew.Trevayne; 09-04-2017 at 01:58 AM.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Filtering with Custom made Combo Box

    See attached your file with some slight modifications: Linked cells for combox changed and columns reference for filter added.
    Option Explicit
    Sub Button6_Click()
    Dim SalesWk  As Worksheet
    Dim DataWk  As Worksheet
    
    Dim SelectionA  As Range
    Dim SubRegion  As Range
    Dim Segment  As Range
    Dim Country  As Range
    Dim SelectionC  As Range
    
    Dim SelectionA_Col   As Integer
    Dim SubRegion_Col   As Integer
    Dim Segment_Col   As Integer
    Dim Country_Col   As Integer
    Dim SelectionC_Col   As Integer
    
    Dim WkRg  As Range
    Dim DispRg  As Range
    Dim LR  As Integer
    Dim LC  As Integer
    
        Set SalesWk = Sheets("Sales Dashboard")
        Set DataWk = Sheets("DataSource")
        With SalesWk
            Set SelectionA = .Range("AAA6")
            Set SubRegion = .Range("AAA7")
            Set Segment = .Range("AAA8")
            Set Country = .Range("AAA9")
            Set SelectionC = .Range("AAA10")
            Set DispRg = .Range("C41")
            
            LR = .Cells(Rows.Count, DispRg.Column).End(3).Row
            LC = DispRg.End(xlToRight).Column
            Set WkRg = Range(DispRg, .Cells(LR, LC))
            WkRg.Offset(1, 0).ClearContents
            
        End With
        SelectionA_Col = SelectionA.Offset(0, 1)
        SubRegion_Col = SubRegion.Offset(0, 1)
        Segment_Col = Segment.Offset(0, 1)
        Country_Col = Country.Offset(0, 1)
        SelectionC_Col = SelectionC.Offset(0, 1)
        With DataWk
            LR = .Cells(Rows.Count, 1).End(3).Row
            LC = .Cells(5, Columns.Count).End(xlToLeft).Column
            Set WkRg = Range(.Range("A5"), .Cells(LR, LC))
            If (.AutoFilterMode) Then .AutoFilterMode = False '  REMOVE  AUTOFILTER  IF  EXIST
            With WkRg
                .AutoFilter Field:=SelectionA_Col, Criteria1:=SelectionA
                .AutoFilter Field:=SubRegion_Col, Criteria1:=SubRegion
                .AutoFilter Field:=Country_Col, Criteria1:=Country
                .AutoFilter Field:=SelectionC_Col, Criteria1:=SelectionC
                .Offset(1, 0).Copy Destination:=DispRg.Offset(1, 0)
            End With
        End With
    End Sub
    Attached Files Attached Files
    - Battle without fear gives no glory - Just try

+ 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: 0
    Last Post: 01-31-2016, 02:36 AM
  2. Custom made macros disable itself everytime I open ppt
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2014, 10:47 AM
  3. Replies: 19
    Last Post: 09-06-2014, 06:30 PM
  4. Update a text field when a selection is made from a Combo Box
    By ASDFG in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-26-2012, 11:17 AM
  5. Finding largest number with custom made ID numbers
    By bigeyedphish in forum Excel General
    Replies: 2
    Last Post: 11-21-2011, 04:06 PM
  6. Excel Custom Made Toolbar
    By drawlings in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-16-2006, 02:25 PM
  7. [SOLVED] How to save a custom made header&footer
    By HeintjeHB in forum Excel General
    Replies: 3
    Last Post: 06-29-2006, 11:30 AM

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