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

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

And add this one line to the top of each procedu

set target = target.cells(1)

This will avoid any problem if you have multiple cells selected when you do the
right click.



Dave Peterson wrote:

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

--

Dave Peterson

Dave

Thanks for that. It worked really well.

I was impressed by your second piece of code. Much neater than my poor
effort and more effective too.

Again, thanks for taking the time to pass on your knowledge.

Regards


Alex

"Dave Peterson" wrote:

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

Don't forget to add that extra line, too. It may come back to haunt you if you
don't.

Alex wrote:

Dave

Thanks for that. It worked really well.

I was impressed by your second piece of code. Much neater than my poor
effort and more effective too.

Again, thanks for taking the time to pass on your knowledge.

Regards

Alex

"Dave Peterson" wrote:

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


--

Dave Peterson