Prev: Active X Controls Shrinking on Excel worksheet
Next: Creating a Pivot Table via Macro - Errors on Recorded Code
From: mooresk257 on 2 Jun 2010 16:16
I have this code to control user access to commands:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

Dim Cmdbar As CommandBar
For Each Cmdbar In Application.CommandBars
Cmdbar.Enabled = False
Next

Application.CommandBars("Cell").Enabled = True

Application.DisplayStatusBar = True
Application.DisplayPasteOptions = True
Application.CutCopyMode = True

With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With

End Sub

I want to restrict right-click menu options to Cut, Copy, and Paste -
specifically paste as values. I've been trying the suggestions I've found for
code solutions related to pop-ups in the VBA help files, and on the MSFT KB,
but I keep getting function errors. This code seems like it would be the
solution but I'm not sure why it is not working:

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

Set copyAndPasteMenu = CommandBars.Add(Name:="Custom",
Position:=msoBarPopup, Temporary:=True)
Set Copy = copyAndPasteMenu.Controls.Add
With Copy
.FaceId = CommandBars("Standard").Controls("Copy").ID
.Caption = "Copy the selection"
End With
Set Paste = copyAndPasteMenu.Controls.Add
With Paste
.FaceId = CommandBars("Standard").Controls("Paste").ID
.Caption = "Paste from the Clipboard"
End With
copyAndPasteMenu.ShowPopup 200, 200

End Sub

Any suggestions?

Thanks,

Scott
 | 
Pages: 1
Prev: Active X Controls Shrinking on Excel worksheet
Next: Creating a Pivot Table via Macro - Errors on Recorded Code