Selecting a call and using info in auto filter custom select

[Guest]

I have a table on left column is name of a server on the right are columns as
to their backup status the list of servers is about 100. I would like it if
an user could click on that cell and my doing so macr would sect the contents
and using that data to use on a custom filter on theraw data so they can view
the details of the of the backups related to that server

I have tried

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim Selected
Set Selected = ActiveCell
Sheets("Raw Data").Select
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell.Select, Operator:=xlAnd
End Sub

but it returns nothing can someone help?

 

[broro183]

Hi Jelinek,

The problem is with the following line:
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell.Select
Operator:=xlAnd

it should work if changed to:
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell.value

or even (based on the fact that the value is normally the defaul
property of the active cell):
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell

I use a similar sort of macro at work to filter quickly, but rathe
than using a change macro I have assigned it to a shortcut key (coul
also be linked to a button on a toolbar), to prevent unintentiona
filtering when users are trying use the right click etc.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Guest]

thanks i will try this, just one thought? will this work if the filter is on
a diffrent worksheet?

Thanks

Simon

 

[broro183]

Hi Simon,

I'm guessing that you will have experimented with your macro by now,
but in case you are still after an answer...

The quick test that I've just done worked for me so I guess you can
filter other sheets, I was on sheet one & used:
Worksheets("sheet2").Range("a1").AutoFilter Field:=2, Criteria1:=
activecell

I think as long as your range includes a cell that is included in the
filtered range it should work - may be wrong though.


btw, if you are interested in a larger more generic filtering tool,
have a look at my homemade version:
http://excelforum.com/showthread.php?t=533724


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Guest]

Rob

thanks for all your help that worked a treat

Thanks

Simon

 

[broro183]

Hi Simon,
No problem - pleased I could help

Rob Brockett
NZ
Always learning & the best way to learn is to experience..