Instead of disabling the Cell commandbar, you can just add:

Cancel = true

to your code.

That suppresses that popup menu.

Kind of like:

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

If Intersect(Target, Me.Range("c:e")) Is Nothing Then Exit Sub

Cancel = True 'stop the popup

If Target.Column = 3 Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Names"
End With
ElseIf Target.Column = 4 Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Suppliers"
End With
ElseIf Target.Column = 5 Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Parts"
End With
End If

End Sub

But since your code looks pretty much the same, you may want to do it like this:

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

Dim myFormula As String

If Intersect(Target, Me.Range("c:e")) Is Nothing Then Exit Sub

Cancel = True 'stop the popup

Select Case Target.Column
Case Is = 3: myFormula = "=Names"
Case Is = 4: myFormula = "=Suppliers"
Case Is = 5: myFormula = "=Parts"
Case Else
MsgBox "design error--contact alex"
Exit Sub
End Select

With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=myFormula
End With

End Sub



Alex wrote:

Excel Helper

I have recently built a spreadsheet with 5 worksheets.

In worksheet 1 I had a set up whereby if a user right-clicked in column
3,4,5 then a drop down menu appeared and the usual short-cut menu was
suppressed.

Here is my code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)

Dim cbBar As CommandBar
Set cbBar = CommandBars("Cell")

If Selection.Column = 3 Then

cbBar.Enabled = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=Names"
End With

ElseIf Selection.Column = 4 Then
cbBar.Enabled = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=Suppliers"
End With

ElseIf Selection.Column = 5 Then
cbBar.Enabled = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=Parts"
End With

Else
cbBar.Enabled = True

End If

End Sub

I have two problems with this code:

1) If I click in columns 3,4 and 5 it works fine. However, if I then go into
a different worksheet and right-click the short cut menu does not appear,
EVEN THOUGH the code above is placed in the Sheet 1 code module. It seems
that once the short-cut menu is disabled it remains that way. Any ideas?

2) Related to point (1), when I closed this workbook and opened up a
separate workbook the right-click short-cut was still disabled. I am aware
that a change to a menu bar will appear in all subsequent workbooks (unless
you use event handling) but was surprised to see that the right-click
disabling had been inherited too. Again, any ideas?

As always I am grateful for any guidance.

Regards

Alex

--

Dave Peterson