deactivate 'commandbars'-shortcutmenu 2

[littlewoman]

using excel 2003-vba

I have a database with a lot of code behind it to automize all. To protect the database and the code behind it against userinterference I have gave excel a 'standalone' appearance by activating a fullscreen mode and disabling the active menubar and fullscreen-menubar.
Fullscreen, to let all displayed menubars disappear but also the titlebar and with it the close-button. The deactivation of the closebutton was nescessary to force the user to use my closebutton which event restores the 'excel appearance'.
I placed a customized commandbar and docked it safely and unremovable on top.
The database is sheetprotected and can only be filtered through autofilter present on the sheets or scrolled down by use of the vertical scrollbar and viewed by use of the customized toolbar.
All the rest need to be done by userforms.

I know there must be fancier ways to accomplish this but dabbling around possible solutions I managed to remove all menubuttons from the main commandbar and needed to recover office to get "files", "edit" etc. back. My conclusion was that this what not something to do with my limited knowledge right now

Well all that remains now is one little irritating problem.

If you rightclick on the empty space next to the commandbar a shortcutmenu appears and let you activate the disabled menubars and also, although sheetcells can't be selected, sheet-rightclicking is possible.

I have no need for shortcuts anywere so is there a way to disable them or atleast just that commandbarselection-one and still be able to activate them again on closure of the workbook? or maybe by deactivating the rightmouse-button perhaps for the time nescessary (guessing wildly.)

and before anyone starts again with the sentence "have you searched...."
yep I've searched: vba-forum, officeforum, faqs and about 50 sites and forums and tutorials on the internet by use of just 1 simple keyword "shortcut" to get as much hits possible.
no luck in this specific.

so anyone with a direct link to usefull info or perhaps better with an answer, plse help?

 

[rmikesmith]

While there may be another way to prevent this, according to Professional Excel Development, by Stephen Bullen, Rob Bovey and John Green, the "fix" is to ensure that your custom commandbar spans the entire width of the docking area, thereby eliminating any space in which a right-click may activate that particular shortcut menu.


Regards,
Mike

 

[littlewoman]

good idea!!

shoot, why didn't I think of that

thnx Mike

 

[littlewoman]

as you might guess by my returning,

it doesn't work :-(
I was sure it would, it seemed so simple of a solution.

But as it turns out I have the habit of rightclicking on the free space yet this event even occurs when rightclicking on buttons and menu's

what an irritating piece of software to get rid off.
plse anyone another idea before I throw this pc out of the window ?
:-s

 

[combo]

Hi,
there are some commandbar(s) properties that can be useful:

1) hide "Customize" option:
Application.CommandBars.DisableCustomize = False

2) apply various protection options (what you probably use):
Application.CommandBars("Standard").Protection = msoBarNoCustomize + msoBarNoMove

3) disable commandbar, it's more than hide it, can't be accessed in 'customize' dialog:
Application.CommandBars("Standard").Enabled = False

combo

 

[combo]

Obviously, the first option should be (the above resets customization):
Application.CommandBars.DisableCustomize = True

combo

 

[rmikesmith]

Combo,

The DisableCustomize property must only be available for versions after XL2000.

Your 3) looks promising. One could loop through all built-in commandbars and disable. Combined with 2) this would certainly limit what a user could do.


Mike

 

[combo]

Mike,
DisableCustomize was introduced in office xp, so can be used in 2003. However, after right-clicking any toolbar, you still get a list of available toolbars, just no access to 'customize' dialog.

combo

 

[littlewoman]

uhm what I used:

CommandBars("Sales").Protection = msoBarNoChangeDock

found it somewhere on the internet and it worked

enabled:
well last time I tried to detect and disable all visible workbars I manage to get a empty one back on enabling again as I mentioned before. That scared me somewhat 'cause I couldn't figure out why this happened, so I returned to the use of fullscreen mode and a standalone appearance. Like this:

'MENUBARS OFF
Application.CommandBars.ActiveMenuBar.Enabled = False
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Visible = False
Application.DisplayFormulaBar = False

'REMOVE SHEETFUNCTIONS
For piCnt = 1 To ActiveWorkbook.Sheets.Count
Sheets(piCnt).Select
With ActiveWindow
.DisplayHeadings = False
.DisplayWorkbookTabs = False
.DisplayHorizontalScrollBar = False
End With
Next piCnt
Sheets(1).Select
ActiveWindow.DisplayVerticalScrollBar = False

very simple but it seemed to do the trick only for that shortcutmenu

well okay in the hope I don't screw up again and need to recover excel for the second time, I'll go and try with those 3 options
with excel 2003 I should be able to I gather.

guys, thnx for all your help, much appreciated
but plse, plse keep your fingers crossed ('cause I can't cross and type at the same time)
let you know what happened :-s

 

[rmikesmith]

I left out a key piece of info; my lapse and no reflection on Mssrs. Bullen, Bovey and Green. The following code will disable the right-click popup of commandbars:

Application.CommandBars("Toolbar List").Enabled = False

The trick of making your customized commandbar cover the available docking space applies to preventing a double-click invoking the Customize dialog.


Hope this helps.
Mike

 

[littlewoman]

okay findings:

added to code:
Sub BarsOff()

Dim Cbar As CommandBar
Dim CbarName As String

For Each Cbar In CommandBars
CbarName = Cbar.Name
Application.CommandBars(CbarName).Enabled = False
Next

End Sub

Sub BarsOn()

Dim Cbar As CommandBar
Dim CbarName As String

For Each Cbar In CommandBars
CbarName = Cbar.Name
Application.CommandBars(CbarName).Enabled = True
Next

End Sub

worked all bars went off and disappeared off the shortcutlist
all except 1: "task pane"
this one can only be turned invisible

with the code for making the customized commandbar I have changed the line

CommandBars("Sales").Protection = msoBarNoChangeDock

into:
CommandBars("Sales").Protection = msoBarNoChangeDock + msoBarNoChangeVisible

and also this one disappears from the shortcutmenu
to do same with "task pane" resulted in the message that protection not worked with this bar

now I don't think the task pane can do much wrong on its own and 'cause I can't figure out why this one is an exception I think I let that one go.

okay the pc may stand a while longer in his place
I thank you all for keeping my sanity
much appreciated

'till we meet again

 

[rmikesmith]

Littlewoman,

Did you try turning off the toolbars list as indicated in my last post?

A suggestion on streamlining: you don't need to separately get a commandbar's name (string) then reference that, to set a property; i.e., instead of

Dim Cbar As CommandBar
Dim CbarName As String

For Each Cbar In CommandBars
    CbarName = Cbar.Name
    Application.CommandBars(CbarName).Enabled = False
Next

you can write

Dim Cbar As CommandBar

For Each Cbar In CommandBars
  CBar.Enabled = False
Next

Regards,
Mike