Working with Menu Bars / Toolbars problem
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
|