Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 21-11-2009
Member
 
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?
Reply With Quote
  #2  
Old 21-11-2009
Member
 
Join Date: May 2008
Posts: 2,293
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
Reply With Quote
  #3  
Old 21-11-2009
Member
 
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
Reply With Quote
  #4  
Old 21-11-2009
Member
 
Join Date: May 2008
Posts: 2,293
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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Automatic filtering on Excel VBA and action button"
Thread Thread Starter Forum Replies Last Post
Automatic filtering in Excel RenNoClue Software Development 4 27-01-2012 08:08 PM
Not able to address vba in the first visible cell in a Column after filtering in Excel SirName MS Office Support 1 25-01-2012 06:27 PM
How to Use Excel filtering to find data quickly manishdk Guides & Tutorials 1 31-12-2009 01:18 PM
Excel autofilter stops filtering roumieh2000 Windows Software 3 15-10-2009 03:31 PM
Sorting and filtering Microsoft Excel CLONEOPS Windows Software 2 09-01-2009 11:52 AM


All times are GMT +5.5. The time now is 12:59 AM.