Select Case on a range - problem

[cdb]

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 Ogilvy]

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

 

[cdb]

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]

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.

 

[cdb]

Tom,

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