Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Disable/enable sheets tab delete sheet (excel 2000)

    This will disable/enable the "delete" and "delete sheet "part form the "Edit "position on "the menu bar".
    Howdoes one disable/enable the command on the "sheet tab" that gives the same result?

    How figured that on out yet?
    I could do with some help.
    Thanks
    Jan

    Sub test1()
    'write down the exact notation in the menu that you want to disable/enable
    'from the left (2) Edit then (12)downwards Delete sheet
    'I think it is language sensitive so you need to put it in English USA
    'this is only for the menu not ok when klicking the sheettab
    CommandBars("Worksheet Menu bar").Controls("Bewerken").Controls("Verwijderen.. .").Enabled = False
    CommandBars("Worksheet Menu bar").Controls("Bewerken").Controls("Blad verwijderen").Enabled = False
    MsgBox ("verder gaan")
    CommandBars("worksheet Menu bar").Controls("Bewerken").Controls("Blad verwijderen").Enabled = True
    '
    CommandBars("Worksheet Menu bar").Controls("Bewerken").Controls("Verwijderen.. .").Enabled = False
    CommandBars("Worksheet Menu bar").Controls("Bewerken").Controls("Verwijderen.. .").Enabled = True
    End Sub

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

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    There is a whole list of right-click popup menus that contain Delete options:
    - Ply (sheet tab)
    - Row (row header)
    - Column (column header)
    - Cell
    The "name" of the delete option in the first one is "Verwijderen" in Dutch, for the other three it is "Verwijderen..."

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    You might check out Jan Karel <!profile=Pieterse>Pieterse<!/profile>'s xlMenuFunDict.zip. This has lists of the various commandbars items as well as function in multiple languages.

    Steve

  4. #4
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    Hans,

    ok I can manage to disable the commands from the menu Bar but you have a set of commands when you clock on the sheets tab-name 1 x right. From this menu you can delete a sheet in the workbook too. I want to prevent. No deletion of a particular sheet. And you cannot protect the sheet with a password too. It has to be worked on by the user.

    Main problem disable commands in the sheettab-menu.

    Any suggestions ?

    Thanks,
    Jan

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

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    Hi Jan,

    The popup menu you refer to is the "Ply" command bar I mentioned in my previous reply. To disable the "Delete" option in this popup menu, use

    Application.CommandBars("Ply").Controls("Delete"). Enabled = False

    in the English language version, or

    Application.CommandBars("Ply").Controls("Verwijder en").Enabled = False

    in the Dutch language version. Use ... = True to enable the option again.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    What about workbook protection? (tools - protection - protect workbook). The user will have full control over the sheet (edit, delete, insert items with no restirction) but can delete the sheet as it is part of the workbook. All the "delete sheets" will be disabled by the workbook protection.

    Steve

  7. #7
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    Hans,

    Application.CommandBars("Ply").Controls("Delete"). Enabled = False
    works great.

    Excellent I learned something today in spit of the very bad weather.
    Returned from work as the roads were to dangerous and icy this morning.
    Took a day off.


    Thanks,
    Jan

  8. #8
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Disable/enable sheets tab delete sheet (excel 2000)

    Steve,

    workbook protection works too. So today I have a double choice to achieve the same goal.
    What can break my day?


    Thanks,
    Jan

Posting Permissions

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