How can I disable the pop up window when right click is initiated in the worksheet cell.
How can I disable the pop up window when right click is initiated in the worksheet cell.
T-ogether
E-veryone
A-chieves
M-ore
One who asks a question is a fool for five minutes; one who does not ask a question remains a fool forever.
[VBA]Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'Disable right-click
Cancel = True
End Sub
[/VBA]
He didn't know it was impossible, so he did it. (Jean Cocteau)
That easy? Thanks...
Originally Posted by tstav
T-ogether
E-veryone
A-chieves
M-ore
One who asks a question is a fool for five minutes; one who does not ask a question remains a fool forever.
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
[vba]
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
[/vba]
T-ogether
E-veryone
A-chieves
M-ore
One who asks a question is a fool for five minutes; one who does not ask a question remains a fool forever.
Solve found a solution...
[vba]
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
[/vba]
T-ogether
E-veryone
A-chieves
M-ore
One who asks a question is a fool for five minutes; one who does not ask a question remains a fool forever.
[VBA]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
[/VBA]
He didn't know it was impossible, so he did it. (Jean Cocteau)
Wow easiest way... Perfect... Thanks once again...
Originally Posted by tstav
T-ogether
E-veryone
A-chieves
M-ore
One who asks a question is a fool for five minutes; one who does not ask a question remains a fool forever.
You should use Me not ThisWorkbook.Worksheets(1)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Correct, xld.
He didn't know it was impossible, so he did it. (Jean Cocteau)
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Correct, xld. No question about it. My mixup.
He didn't know it was impossible, so he did it. (Jean Cocteau)
If this needs more than a quick answer, I'll start a new thread.Originally Posted by xld
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.?
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks for the "me" explanation, my experimenting will now make more sense.
I'm as much looking for help with Help, often frustrating. No matter how I searched VBA help, I got no hits whatsoever.Originally Posted by xld
Where in the Index or Table of Contents, or what search word(s) did you use to find it please?
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?)