Disable/enable sheets tab delete sheet (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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…”

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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(“Verwijderen”).Enabled = False

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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(“Verwijderen”).Enabled = False

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

Reply | Quote

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

Reply | Quote

Steve,

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

Thanks,
Jan

Reply | Quote

Steve,

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

Thanks,
Jan

Reply | Quote

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

Reply | Quote

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…”

Reply | Quote

You might check out Jan Karel Pieterse‘s xlMenuFunDict.zip. This has lists of the various commandbars items as well as function in multiple languages.

Steve

Reply | Quote

You might check out Jan Karel Pieterse‘s xlMenuFunDict.zip. This has lists of the various commandbars items as well as function in multiple languages.

Steve

Reply | Quote