Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Menu Bars / Toolbars problem
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminating Toolbars,Sheet Tabs,Scroll Bars. etc. | Excel Programming | |||
Lost all my menu bars and tool bars | Excel Discussion (Misc queries) | |||
Menu/toolbars/macros defined in Excel 2003 not working in other versions? | Excel Programming | |||
New Menu on Worksheet & Chart Menu Bars | Excel Programming | |||
linking chart menu bars and worksheet menu bars | Excel Programming |