Solved: Right Click on cell

[jammer6_9]

How can I disable the pop up window when right click is initiated in the worksheet cell.

[tstav]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Disable right-click
Cancel = True
End Sub

[jammer6_9]

That easy? :banghead: Thanks...


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Disable right-click
Cancel = True
End Sub

[jammer6_9]

Now what I want is column B right click will be disabled and the rest of the sheet columns right click enabled. Tried below unsuccessful code :banghead:


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rSearch As Range
Set rSearch = Sheets("Sheet1").Range("b65536").End(xlUp)
If ActiveCell.Address = rSearch.Address Then
Cancel = True
Else
Cancel = False
End If
End Sub

[jammer6_9]

Solve found a solution...:whistle:


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim rsearch As Range
Dim tf As Boolean
Set rsearch = Sheet1.Range("B6:B100")
tf = FindAddress(rsearch)
If tf = True Then
Cancel = True
Else
Cancel = False
MsgBox ("You are not on the range... Try again..."), vbExclamation, "anas"
End If
If ActiveCell.Value = "" Then
Else
Range("c2").Value = ActiveCell.Value
UserForm1.Show
End If
End Sub


Function FindAddress(ByVal Target As Range) As Boolean
For Each cell In Target
If ActiveCell.Address = cell.Address Then
FindAddress = True
Exit For
Else
FindAddress = False
End If
Next cell
End Function

[tstav]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Disable right-click only for the second column of a specific Worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'You may change the <Worksheets(1)> qualifier as you see fit
If Not Application.Intersect(Target, ThisWorkbook.Worksheets(1).Columns(2)) Is Nothing Then
Cancel = True
End If
End Sub

[jammer6_9]

Wow easiest way... Perfect... Thanks once again...


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Disable right-click only for the second column of a specific Worksheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'You may change the <Worksheets(1)> qualifier as you see fit
If Not Application.Intersect(Target, ThisWorkbook.Worksheets(1).Columns(2)) Is Nothing Then
Cancel = True
End If
End Sub

[Bob Phillips]

You should use Me not ThisWorkbook.Worksheets(1)

[tstav]

Correct, xld.

[Bob Phillips]

No, it os worksheet event code, so it would be in the sheet module, which is a class module. It would then refer to the containing object, the worksheet that it is in.

[tstav]

Correct, xld. No question about it. My mixup.

[AAM]

You should use Me not ThisWorkbook.Worksheets(1) ...
It would then refer to the containing object, the worksheet that it is in.

If this needs more than a quick answer, I'll start a new thread.

Over time I've come to understand a little about "me" in VBA code, but I've never been able to find anything in the VBA help files explaining it. Your description above leads me to believe it can be used in more than forms.

Can you point me to any further explanation, examples, etc.?

[Bob Phillips]

Help says it all

The Me keyword behaves like an implicitly declared variable. It is automatically available to every procedure in a class module. When a class can have more than one instance, Me provides a way to refer to the specific instance of the class where the code is executing. Using Me is particularly useful for passing information about the currently executing instance of a class to a procedure in another module.

A form is a class, a sheet is a class, ThisWorkbook is a class, so Me applies within each of these.

[AAM]

Thanks for the "me" explanation, my experimenting will now make more sense.


Help says it all
I'm as much looking for help with Help, often frustrating. No matter how I searched VBA help, I got no hits whatsoever.

Where in the Index or Table of Contents, or what search word(s) did you use to find it please?

[AAM]

I just typed "me" in a blank module and hit F1, and voila!

But I still can't find it using the Search dialog or locate that page in the Index or Table of Contents.

Regardless, I won't forget the F1 approach.

(why is it that no matter how hard I try, I often can't answer my own questions until I publicly ask in this or other forums?)