Adding VB Buttons and linking to a field on Excel.

[darejack2001]

Hello guys,

Below given is a snapshot of what i actually need to perform,

https://picasaweb.google.com/113572888166861387898/ExcelTips#5670678293710350690

Circled in green are three vba buttons i need to link with the 'Status' column.

When clicked on "Passed" button it should display only the rows with 'Status=Passed'

When clicked on "Failed" button it should display only the rows with 'Status=Failed'

When clicked on 'Show All' it should display all rows.(Need this button as when i click on Passed it would only show 'Passed' and then if i wish to see all rows it would be helpful if i have this button)

Also it would be very helpful if a small counter window could be linked with each of these button to show count for number of passed or failed status.

I can also use filters for the same but i need to make it more user friendly so i need this working...!!

It would be of great help if any1 of u pro's can get me a solution to it..help appreciated..thank you...!!

 

[Andrew Poulsom]

Just record a macro while autofiltering manually, then move the appropriate lines of code into the CommandButtons' Click event procedures.

 

[darejack2001]

Thank you for your reply sir, but i am a newbie to excel so it would be of great help if u could give me steps/procedure for doing same...!!

 

[Andrew Poulsom]

You don't know how to record a macro?

http://office.microsoft.com/en-us/h...they-are-and-why-to-use-them-HA010007210.aspx

 

[darejack2001]

No i dnt knw...!!

anyways i will try to learn from the link which u have provided..thanks...!!

 

[darejack2001]

Hey..thanks a ton...i finally managed to do what was required...the only concern now is with the counter...

https://picasaweb.google.com/113572888166861387898/November32011#5670752314743553570

I need to place a counter in cell G2 in such a way that suppose when clicked on 'Passed' button it should count the number of passed cases and display it in G2.

I am excited about it..pls do help..thanks...!!

 

[darejack2001]

Also i need to protect these buttons (Passed, Failed and See All) so that no one could modify its coding structure..i tried to protect the cell under which i have this button but it eventually stop the buttons functionality...!!

 

[Andrew Poulsom]

To count the number of visible items you can use a formula like:

=SUBTOTAL(3,G6:G13)

Expand the range reference to suit.

To protect your code go to the Visual Basic Editor, select your workbook and choose Tools|VBA Project Properties. On the Protection tab check 'Lock project for viewing', enter and confirm a password and click OK. You will need to save, close and reopen the workbook for the protection to take effect.

 

[darejack2001]

Gosh...m surprised wit things that could be done with excel..u r a real pro sir..thanks a lot for ur help...!!

Can by any chance we lock the positions of these button...??

 

[Andrew Poulsom]

Where are the buttons from - Control Toolbox or Forms Toolbar?