Select Case on a range - problem

I am trying to use a Select Case statement that activates when someone right
clicks on a cell and depending what cell is clicked on a different thing
runs. Unfortunately I either can't get it to run at all or it runs through
all the code.

Can someone please give me some code to fix this (e.g. if cell C5 is right
clicked on I want it to go to a range on Sheet 1, but if cell C6 is clicked
on I want it to go to a different range on Sheet 2)

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Select Case Target.Address
case "$C$5"
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Select
Cancel = True
Case "$C$6"
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A1").Select
Cancel = True
End Select
End Sub

--
Regards,
Tom Ogilvy

"cdb" <cdb@discussions.microsoft.com> wrote in message
news:C2A26FF2-3C6E-4329-B491-F232C8E2ED64@microsoft.com...
> I am trying to use a Select Case statement that activates when someone
right
> clicks on a cell and depending what cell is clicked on a different thing
> runs. Unfortunately I either can't get it to run at all or it runs through
> all the code.
>
> Can someone please give me some code to fix this (e.g. if cell C5 is right
> clicked on I want it to go to a range on Sheet 1, but if cell C6 is
clicked
> on I want it to go to a different range on Sheet 2)

Tom,

Many thanks - does exactly what was requested. As a little refinement, is it
possible to do this is clicked within a named range??
(E.g. Instead of using $C$5, can I have it work if clicked on any cells in a
named range of Tuesday?)

"Tom Ogilvy" wrote:

> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
> Boolean)
> Select Case Target.Address
> case "$C$5"
> Worksheets("Sheet1").Activate
> Worksheets("Sheet1").Range("A1").Select
> Cancel = True
> Case "$C$6"
> Worksheets("Sheet2").Activate
> Worksheets("Sheet2").Range("A1").Select
> Cancel = True
> End Select
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "cdb" <cdb@discussions.microsoft.com> wrote in message
> news:C2A26FF2-3C6E-4329-B491-F232C8E2ED64@microsoft.com...
> > I am trying to use a Select Case statement that activates when someone
> right
> > clicks on a cell and depending what cell is clicked on a different thing
> > runs. Unfortunately I either can't get it to run at all or it runs through
> > all the code.
> >
> > Can someone please give me some code to fix this (e.g. if cell C5 is right
> > clicked on I want it to go to a range on Sheet 1, but if cell C6 is
> clicked
> > on I want it to go to a different range on Sheet 2)
>
>
>

Private Sub Worksheet_BeforeRightClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rng as Range, nm as Name
Dim sName as String
for each nm in ThisWorkbook.Names
on Error Resume Next
set rng = nm.RefersToRange
on Error goto 0
if not rng is nothing then
if rng.parent.name = target.parent.name then
if not intersect(target,rng) is nothing then
sName = lcase(nm.Name)
exit for
end if
end if
end if
Next

if sName = "" then exit sub
Select Case sName
case "monday"
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").Select
Cancel = True
Case "tuesday"
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A1").Select
Cancel = True
End Select
End Sub

A single cell should not be included in two name definitions.

--
Regards,
Tom Ogilvy

"cdb" <cdb@discussions.microsoft.com> wrote in message
news:9BC5053F-E8AA-402C-8CEF-2BBAE41A1FF2@microsoft.com...
> Tom,
>
> Many thanks - does exactly what was requested. As a little refinement, is
it
> possible to do this is clicked within a named range??
> (E.g. Instead of using $C$5, can I have it work if clicked on any cells in
a
> named range of Tuesday?)
>
> "Tom Ogilvy" wrote:
>
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
> > Boolean)
> > Select Case Target.Address
> > case "$C$5"
> > Worksheets("Sheet1").Activate
> > Worksheets("Sheet1").Range("A1").Select
> > Cancel = True
> > Case "$C$6"
> > Worksheets("Sheet2").Activate
> > Worksheets("Sheet2").Range("A1").Select
> > Cancel = True
> > End Select
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "cdb" <cdb@discussions.microsoft.com> wrote in message
> > news:C2A26FF2-3C6E-4329-B491-F232C8E2ED64@microsoft.com...
> > > I am trying to use a Select Case statement that activates when someone
> > right
> > > clicks on a cell and depending what cell is clicked on a different
thing
> > > runs. Unfortunately I either can't get it to run at all or it runs
through
> > > all the code.
> > >
> > > Can someone please give me some code to fix this (e.g. if cell C5 is
right
> > > clicked on I want it to go to a range on Sheet 1, but if cell C6 is
> > clicked
> > > on I want it to go to a different range on Sheet 2)
> >
> >
> >

Tom,

Many many thanks - you're an absolute star!!

"Tom Ogilvy" wrote:

> Private Sub Worksheet_BeforeRightClick( _
> ByVal Target As Range, Cancel As Boolean)
> Dim rng as Range, nm as Name
> Dim sName as String
> for each nm in ThisWorkbook.Names
> on Error Resume Next
> set rng = nm.RefersToRange
> on Error goto 0
> if not rng is nothing then
> if rng.parent.name = target.parent.name then
> if not intersect(target,rng) is nothing then
> sName = lcase(nm.Name)
> exit for
> end if
> end if
> end if
> Next
>
> if sName = "" then exit sub
> Select Case sName
> case "monday"
> Worksheets("Sheet1").Activate
> Worksheets("Sheet1").Range("A1").Select
> Cancel = True
> Case "tuesday"
> Worksheets("Sheet2").Activate
> Worksheets("Sheet2").Range("A1").Select
> Cancel = True
> End Select
> End Sub
>
> A single cell should not be included in two name definitions.
>
> --
> Regards,
> Tom Ogilvy
>
> "cdb" <cdb@discussions.microsoft.com> wrote in message
> news:9BC5053F-E8AA-402C-8CEF-2BBAE41A1FF2@microsoft.com...
> > Tom,
> >
> > Many thanks - does exactly what was requested. As a little refinement, is
> it
> > possible to do this is clicked within a named range??
> > (E.g. Instead of using $C$5, can I have it work if clicked on any cells in
> a
> > named range of Tuesday?)
> >
> > "Tom Ogilvy" wrote:
> >
> > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
> > > Boolean)
> > > Select Case Target.Address
> > > case "$C$5"
> > > Worksheets("Sheet1").Activate
> > > Worksheets("Sheet1").Range("A1").Select
> > > Cancel = True
> > > Case "$C$6"
> > > Worksheets("Sheet2").Activate
> > > Worksheets("Sheet2").Range("A1").Select
> > > Cancel = True
> > > End Select
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "cdb" <cdb@discussions.microsoft.com> wrote in message
> > > news:C2A26FF2-3C6E-4329-B491-F232C8E2ED64@microsoft.com...
> > > > I am trying to use a Select Case statement that activates when someone
> > > right
> > > > clicks on a cell and depending what cell is clicked on a different
> thing
> > > > runs. Unfortunately I either can't get it to run at all or it runs
> through
> > > > all the code.
> > > >
> > > > Can someone please give me some code to fix this (e.g. if cell C5 is
> right
> > > > clicked on I want it to go to a range on Sheet 1, but if cell C6 is
> > > clicked
> > > > on I want it to go to a different range on Sheet 2)
> > >
> > >
> > >
>
>
>