Disable Editing tool bars

[Mike Rogers]

I posted the other day and got some direction. Thank you Peter Ellis. Now I
need some help with what I found. I found a set of macros that should
disable on opening, and enable on closing. The problem is they don't work
and I am not literate in macro syntax. I have researched and all I have found
says they should work. Here is what I am trying to work with:

Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("Formatting").Enabled = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Standard").Enabled = True
Application.CommandBars("Formatting").Enabled = True
End Sub
This is in a standard module.

I also nee to encorporate code to stop "copy & Paste from working, or at
least the window that come from a right mouse click.

Mike Rogers

 

[Mike Rogers]

Opps, at "Application.CommandBars("Worksheet Menu Bar").Enabled = True
This should be "False"

 

[Gord Dibben]

Mike

Take the code out of the standard module and place it in the Thisworkbook
module.

To disable the right-click menu.......

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


Gord Dibben Excel MVP

 

[Mike Rogers]

Gord, Thanks a ton...it works!!! (but you knew it would)

One more question, and I know there may not be an answer. The formula bar
can be removed via tools/options etc. If the user has this selected for
their perferences can I un-select it some how when running my application and
the put it back when my application is closed.

 

[Gord Dibben]

Mike

In the Sub workbook_open add this line

Application.DisplayFormulaBar = False

Change to = True in the Sub workbook_beforeclose


Gord

 

[Mike Rogers]

Gord that works perfect!!!!

Now I am trying to follow the syntax. Would I Add the line
"Application.DisplayStatusbar = False" to remove the status bar on this form?
Where do you look to find the proper syntax for writing this stuff? I have
looked in Help and think sometimes I am to inexperienced to understand it.
You know, you need some experience to be able to ask an intelligent question.

Hey, thanks a million for your help, I really do appreciate it.

Mike

 

[Mike Rogers]

Gord,
I just noticed that when I close with the "X" in the upper right corner, ( I
have no other way as all the tool bars are gone) as soon as I click the "X",
the formula bar and the status bar return, and if I cancel out they remain.
Whats up with that?

Mike

 

[Gord Dibben]

Mike

If you turned those back to True in your BeforeClose sub this would fire when
you closed Excel by using the "X".

I assume you are trying to keep user-interaction as minimal as possible.

You have to allow for things like users closing then cancelling when asked if
they want to save changes etc.

I'm not really the guy you should be talking to about this.

My VBA skills are minimal and have to poke and prod my way around much like
you are doing.

Perhaps copying some of this thread and moving over to the
microsoft.public.excel.programming group would get you better service,
although most of the regulars monitor all Excel groups so may jump in when
they see I have bailed out<g>.


Gord

 

[Mike Rogers]

Hey Gord, I understand what you are saying, and you are right, I am just
trying to keep user interaction minimal. So this really works for what I am
doing. I hope to some day be able to "poke and prod" as well as you. I am
taking advantage of each opportunity to learn from as many of you guys as I
can, so I really do appreciate your help and direction, you have been great...

Thanks again
Mike