Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: deactivating menus

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Will simple protection be enough?
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I particularly want to disable the pull down menus

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •