Selecting a call and using info in auto filter custom select

[Jelinek]

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 default property of the active cell):
Selection.AutoFilter Field:=1, Criteria1:=ActiveCell

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

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

[Jelinek]

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

Thanks

Simon

"broro183" wrote:

>
> 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 default
> property of the active cell):
> Selection.AutoFilter Field:=1, Criteria1:=ActiveCell
>
> I use a similar sort of macro at work to filter quickly, but rather
> than using a change macro I have assigned it to a shortcut key (could
> also be linked to a button on a toolbar), to prevent unintentional
> filtering when users are trying use the right click etc.
>
> hth
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=543250
>
>

[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...

[Jelinek]

Rob

thanks for all your help that worked a treat

Thanks

Simon

"broro183" wrote:

>
> 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...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=543250
>
>

[broro183]

Hi Simon,
No problem - pleased I could help :-)

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