deactivating menus

[Peter99]

I have a worksheet that I do not want the user to change by being able to access menu or sheet options

can anyone HELP

[Tom Schreiner]

Will simple protection be enough?
Tom

[nancyo]

You can protect the sheet (with an optional password):

Tools - protection - protect sheet - password - confirm password.

You can allow access to specific cells by formatting those cells as "unlocked" (format cells - protection - locked). All cells by default are "locked" when the protection is applied to a spreadsheet.

[Peter100]

I particularly want to disable the pull down menus

[Tom Schreiner]

Hi
This little macro will hide every command bar which is visible...

Of course you will need to make some of these visible when the workbook is closed.

Is specific to the application, not just one workbook...

Sub HideBars()
Dim Cbar
For Each Cbar In CommandBars
If CommandBars(Cbar.Name).Visible = True Then _
CommandBars(Cbar.Name).Visible = False
Debug.Print Cbar.Name
Next
End Sub


To make visible pick the commandbars from below to appear before the workbook closes.

See the list of the most common/standard command bars names below...
To make visible again...
For ex. The third commandbar listed below,
use the following syntax..

CommandBars("Standard").Visible = True

Worksheet Menu Bar
Chart Menu Bar
Standard
Formatting
PivotTable
Chart
Reviewing
Forms
Stop Recording
External Data
Auditing
Full Screen
Circular Reference
Visual Basic
Web
Control Toolbox
Exit Design Mode
Refresh
Drawing
Query and Pivot
PivotChart Menu
Workbook tabs
Cell
Column
Row
Cell
Column
Row
Ply
XLM Cell
Document
Desktop
Nondefault Drag and Drop
AutoFill
Button
Dialog
Series
Plot Area
Floor and Walls
Trendline
Chart
Format Data Series
Format Axis
Format Legend Entry
Formula Bar
PivotTable Context Menu
Query
Query Layout
AutoCalculate
Object/Plot
Title Bar (Charting)
Layout
Pivot Chart Popup
Phonetic Information
WordArt
Picture
Shadow Settings
3-D Settings
Borders
Chart Type
Pattern
Font Color
Fill Color
Line Color
Order
Nudge
Align or Distribute
Rotate or Flip
Lines
Connectors
AutoShapes
Callouts
Flowchart
Block Arrows
Stars & Banners
Basic Shapes
Shapes
Inactive Chart
Excel Control
Curve
Curve Node
Curve Segment
Pictures Context Menu
OLE Object
ActiveX Control
WordArt Context Menu
Rotate Mode
Connector
Script Anchor Popup
EuroPlaceholder_2000
Undo
Wi&zard
Add Command
Built-in Menus
System
Clipboard

Hope this helps you,
Tom

[Peter100]

Thanks although I'm a little hesitant about not being able to turn them back on again.

I'm not quite sure how to implement the bars I want switched off. where and how Do I list these ie. before the EndSub ?

could you be so kind as to show me the procedure for say turning off the "standard" Toolbar
Then turning it back on before closing the workbook

[Tom Schreiner]

On 2002-04-23 00:22, Peter100 wrote:
Thanks although I'm a little hesitant about not being able to turn them back on again.

I'm not quite sure how to implement the bars I want switched off. where and how Do I list these ie. before the EndSub ?

could you be so kind as to show me the procedure for say turning off the "standard" Toolbar
Then turning it back on before closing the workbook

I would turn them off in the WorkBook_Activate
event and turn them back on in the WorkBook_Deactivate event

Private Sub Workbook_Activate()
CommandBars("Standard").Visible = False
End Sub

Private Sub Workbook_Deactivate()
CommandBars("Standard").Visible = True
End Sub

Use the activate and deavtivate events incase the user has other workbooks open at the same time. This way the other workbooks will have the standard bar available...

Just substitue any of the names, in the above list from the previous post, in place of "Standard" to make any of the other bars invisible...

Hope this helps,
Tom

[ This Message was edited by: TsTom on 2002-04-23 00:34 ]

[Nathan B]

I've used this thread to hide the command menu bar in an excel workbook. It works great. However, i would like to keep the minimize icon that is located in the right corner of the command menu bar. Is there any way to do this?

Thanks

[Peter100]

to TS Tom
If I read you correctly all I have to do is
insert the activate and deactivate subs into the "this workbook" code which I have done
but get the error "object variable or with block variable not set"

Any ideas where I'm going wrong

[Tom Schreiner]

Sorry, did not test it...
this works:

Private Sub Workbook_Activate()
Application.CommandBars("Standard").Visible = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Standard").Visible = True
End Sub

Tom