Results 1 to 4 of 4

Thread: Automatic filtering on Excel VBA and action button

  1. #1
    Join Date
    Jun 2009
    Posts
    83

    Automatic filtering on Excel VBA and action button

    I'm newbie in VBA and I am having a small problem. I try to automate a function of active filtering data with action buttons positioned next to a table summary of these data. The easiest way is to see the example in attachment. Can you give me step by step explanations of programming lines?

  2. #2
    Join Date
    May 2008
    Posts
    2,297

    Re: Automatic filtering on Excel VBA and action button

    Your request is actually very simple. If I may, I suggest you simplify a little:
    For example, put only one button, which captures the value of the cell selected, go to the results sheet, and filter the array. Add 1 button on this page to cancel the filter and go to page summary.

    I made a macro to filter your table, and to create buttons, + 1 cancel button. Select the macro with your mouse, copy them, open your file, do Alt + F11, look left column: you see the name of your file, the name of the leaves, and "ThisWorkbook". dbl-click this entry and ctrl + V on the page. Save the file. ds-click the macro Buttons_Of_FILTER, hit F5, you have your buttons, click in the cell of choice sheet "Summary", click on the button, you have your result.

    Code:
    Sub FILTER () 
    'Click in the cell of choice sheet "Summary" 
    Sun Mycell As Range 
    Sheets ("Summary"). Select 
    Set Mycell = ActiveCell.Offset (0, 1) 
    Mycell.Value = "" 'supp value next to the Selection 
    Choice = ActiveCell.Value 
    Column = 4 ' 
    'Column = ActiveCell.Column 
    Sheets ("Result"). Select 
    Range ("A1"). CurrentRegion.Select 
    Selection.AutoFilter 
    Selection.AutoFilter Field: = Column, Criteria1: = Selection, Operator: = xlAnd 
    ''''''''''''''''''''''''''''''' 
    nbrangs Selection.SpecialCells = (xlCellTypeVisible). Rows.Count 'Number of rows selected 
    Mycell.Value = nbrangs 'record this nbr collar B synthesis side of the cell selected. 
    ''''''''''''''''''''''''''' if you want to remove the filter and go directly to the fl synthesis, removes the quotes at the beginning of lines selected. 
    'Selection.AutoFilter 
    'Sheets ( "Summary"). Select 
    
    End Sub 
    
    Sub Buttons_Of_FILTER () 
    
    'I made 2 macros installing these buttons on your sheets: 
    Sheets ( "Summary"). Select 
    Rows ( "1:1"). RowHeight = 25 
    'Application.CommandBars ( "Forms"). Visible = True 
    ActiveSheet.Buttons.Add (309.75, 6 215.25, 19). Select 
    Selection.OnAction = "ThisWorkbook.FILTER" 
    Selection.Characters.Text = "FILTER" 
    With Selection.Characters (Start: = 1, Length: = 8). Font 
    . FontStyle = "Bold" 
    . Size = 14 
    End With 
    Range ( "D1"). Select 
    
    Sheets ( "Result"). Select 
    Rows ( "1:1"). RowHeight = 25 
    ActiveSheet.Buttons.Add (309.75, 6 215.25, 19). Select 
    Selection.OnAction = "ThisWorkbook.NO_Filter" 
    Selection.Characters.Text = "Remove Filter" 
    With Selection.Characters (Start: = 1, Length: = 15). Font 
    . FontStyle = "Bold" 
    . Size = 14 
    End With 
    Range ( "D1"). Select 
    End Sub 
    
    Sub NO_Filter () 
    Sheets ( "Result"). Select 
    Range ( "A1"). Select 
    Selection.AutoFilter Field: = 1, Criteria1 :="", Operator: = xlAnd 
    Selection.AutoFilter 
    Sheets ( "Summary"). Select 
    End Sub

  3. #3
    Join Date
    Jun 2009
    Posts
    83

    Re: Automatic filtering on Excel VBA and action button

    I do not know how to thank you firstly to the quality of your information and of course your reaction except many thanks!

    I try to improve on VBA I have made a bit of programming in Basic was younger in that your explanation is perfect!

    Thank you again

  4. #4
    Join Date
    May 2008
    Posts
    2,297

    Re: Automatic filtering on Excel VBA and action button

    I watched the macro once again, especially the really check with another check. I found the results are false. So I've redone a macro direct and reliable than here. And deletes the macro filter in the last 4 lines.

    Code:
    Sub Nb_par_Category () 
    Sheets ( "Summary"). Select 
    Range ( "A2"). Select 
    ActiveCell.CurrentRegion.Columns (2). FormulaR1C1 = "= COUNTIF (Result! C4: C4, RC1)" '(formula NB.SI) 
    ActiveCell.CurrentRegion.Columns (2). ActiveCell.CurrentRegion.Columns Value = (2). Value 
    Range ( "B1") = "number" 
    
    End Sub
    You must not enter the 4 last lines, and remove or disable them by putting an apostrophe in front of the line, or by entering just before Exit Sub, which is out of the macro.

    I also understand why you want or wanted to make a filter if it was just to see no. of lines per call, or if there is another purpose, and know why you need 2 sheets different to do your sorting, this course only to help you optimize your need. if only because it seemed to you, no one can do better bcp.

Similar Threads

  1. Automatic filtering in Excel
    By RenNoClue in forum Software Development
    Replies: 4
    Last Post: 27-01-2012, 08:08 PM
  2. Replies: 1
    Last Post: 25-01-2012, 06:27 PM
  3. How to Use Excel filtering to find data quickly
    By manishdk in forum Guides & Tutorials
    Replies: 1
    Last Post: 31-12-2009, 01:18 PM
  4. Excel autofilter stops filtering
    By roumieh2000 in forum Windows Software
    Replies: 3
    Last Post: 15-10-2009, 03:31 PM
  5. Sorting and filtering Microsoft Excel
    By CLONEOPS in forum Windows Software
    Replies: 2
    Last Post: 09-01-2009, 11:52 AM

Tags for this Thread

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
  •  
Page generated in 1,643,009,838.68623 seconds with 16 queries