PDA

View Full Version : Solved: Excel 2003/2007 - Is it possible to change the caption to a right click menu command?



frank_m
10-20-2010, 07:32 AM
The right click cell command has been causing my users some problem's because it shifts cells without any confirmation warning. To deal with that I have put together the code below to use in the ThisWorkbook open event, as to offer a warning to the user that using the delete command will corrupt the other data on the spread sheet.

I am not concerned much about protecting against other methods of deleting, as it is only the right click command that is easily clicked accidentally.

I do not want to disable it as that some times causes it's own set of headaches, do to occasional inconsistencies when restoring it.

Using the code below does pretty close to what I want,
but if possible, I would like to also change the caption on the built in Delete command from "Delete", to something like "Delete<--Only to be used by an Administrator"

Also it should be noted that (other than my intitial use of the ThisWorkbook open event), I cannot use any event driven code as some times spreadsheet cell editing is done in design mode.

Private Sub Workbook_Open()

Dim MyItem As CommandBarControl
CommandBars("Cell").Reset
Application.CommandBars("Cell").FindControl(Id:=292).Delete
CommandBars("Cell").Controls.Add Type:=msoControlButton, Id:=292, _
Before:=6
Set MyItem = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlButton, Before:=6)
MyItem.Caption = "**DO NOT USE Delete** below, it Will Corrupt Other Data"
Set MyItem = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlButton, Before:=8)
MyItem.Caption = "**DO NOT USE Delete** above, it Will Corrupt Other Data"

End Sub

Bob Phillips
10-20-2010, 08:16 AM
Dim MyItem As CommandBarControl
Set MyItem = Application.CommandBars("Cell").FindControl(ID:=292)
MyItem.Caption = "Delete<--Only to be used by an Administrator"

frank_m
10-20-2010, 08:42 AM
Perfect

Thanks a lot xld