deactivating menus

Peter99

New Member
Joined
Mar 31, 2002
Messages
7
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top