Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disable Worksheet tab right click menu and... (Excel 2000 - 2003)

    I am trying to find a way to disable/enable the menu items when right clicking a worksheet tab. For example I tried:
    thisworkbook.Application.CommandBars("Workbook tabs").Controls("Move or Copy...").Enabled = False
    thisworkbook.Application.CommandBars("Workbook tabs").Controls("Select All Sheets").Enabled = False

    But this doesn't work.

    I also have a PC with Windows XP, Offcie 2003 where disabling of the shortcut keys of copy and paste do not work. The keys are disabled on other PCs but not this one, very strange.

    thisworkbook.Application.OnKey "^{x}", "" ' Ctrl+X Cut

    thisworkbook.Application.OnKey "^{c}", "" ' Ctrl+C Copy

    thisworkbook.Application.OnKey "^{v}", "" ' Ctrl+V Paste

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable Worksheet tab right click menu and... (Excel 2000 - 2003)

    1) The Workbook Tabs command bar lists worksheet names, not commands. The one you want is called Ply. You can use

    CommandBars("Ply").Controls("Move or Copy...").Enabled = False

    (no need to prefix it with ThisWorkbook.Application) or

    CommandBars.FindControl(ID:=848).Enabled = False

    Similar for "Select All Sheets", its ID is 946.

    2) I can't explain why disabling the shortcut keys wouldn't work (you don't need the prefix ThisWorkbook, but that doesn't matter).

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Worksheet tab right click menu and... (Excel 2000 - 2003)

    Greetings,
    Along the same lines. Is it possible to lock the tab description from editing from general users of the file, an allow 'an admin' type of person to unlock and edit?

    Did not see the commands in the tab menu..

    thanks,
    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable Worksheet tab right click menu and... (Excel 2000 - 2003)

    What do you mean by tab description? The name of the worksheet?

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disable Worksheet tab right click menu and... (Excel 2000 - 2003)

    Yes.

    The workbook macros I use require the name of the worksheet to remain as I created the file. Need to not allow editing of the name except by me.

    Brad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Disable Worksheet tab right click menu and... (Excel 2000 - 2003)

    You can protect the structure of the workbook and set a password to unprotect it. Select Tools | Protection | Protect Workbook. Tick Structure to prevent users from creating, renaming, moving and deleting worksheets.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Disable Worksheet tab right click menu and...

    You can also use the codename in your code rather than referring to the tab name - e.g.:
    <code>Sheet1.Range("A1") = 5</code>

    rather than

    <code>Worksheets("Sheet1").Range("A1") = 5</code>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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