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)

Select Case on a range - problem
 

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" wrote in message
...
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)

Select Case on a range - problem
 

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" wrote in message
...
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)

Select Case on a range - problem
 

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" wrote in message
...
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" wrote in message
...
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)

Select Case on a range - problem
 

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" wrote in message
...
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" wrote in message
...
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)