I need to disable hide and unhide commands from the context
menu as well as from main menu for the users of one work book.
With this they should not be able to unhide rows or columns
which I have made hidden by a macro.
How to acheive it?
But at the same time, I want to use these commands programatically
from a macro befor save. Also this commands should be available
to user when they activate any other workbook.
Is it possible?
Regards,
Shetty

Hi
Could you not protect the worksheet and set the "UserInterfaceOnly" to true?
If not you may find this link useful
http://www.tek-tips.com/faqs.cfm?spid=707&sfid=4841

;-)


Shetty wrote in message
m...
I need to disable hide and unhide commands from the context
menu as well as from main menu for the users of one work book.
With this they should not be able to unhide rows or columns
which I have made hidden by a macro.
How to acheive it?
But at the same time, I want to use these commands programatically
from a macro befor save. Also this commands should be available
to user when they activate any other workbook.
Is it possible?
Regards,
Shetty

Thnks for your suggession.
But, I need to do other operations by sheetchange event which
is not working with the protect and "UserInterfaceOnly"=true.
I intend to hide some rows with workbook open event and disable
users to insert/unhide any rows/columns. If user inserts/delets
any row/column, my entire programm gets disturbed and gives
unexpected and wrong results.

Thanks again.
Regards,
Shetty


"Loomah" wrote in message ...
Hi
Could you not protect the worksheet and set the "UserInterfaceOnly" to true?
If not you may find this link useful
http://www.tek-tips.com/faqs.cfm?spid=707&sfid=4841

;-)


Shetty wrote in message
m...
I need to disable hide and unhide commands from the context
menu as well as from main menu for the users of one work book.
With this they should not be able to unhide rows or columns
which I have made hidden by a macro.
How to acheive it?
But at the same time, I want to use these commands programatically
from a macro befor save. Also this commands should be available
to user when they activate any other workbook.
Is it possible?
Regards,
Shetty

Shetty
Use this code to disable/enable the row & column hide & unhide commands

Sub Disable()
Enable_Disable_Commands 883, False 'row hide
Enable_Disable_Commands 884, False 'row unhide
Enable_Disable_Commands 886, False 'col hide
Enable_Disable_Commands 887, False 'col unhide
End Sub

Sub Enable()
Enable_Disable_Commands 883, True 'row hide
Enable_Disable_Commands 884, True 'row unhide
Enable_Disable_Commands 886, True 'col hide
Enable_Disable_Commands 887, True 'col unhide
End Sub

Sub Enable_Disable_Commands(id As Integer, Enab As Boolean)
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, id:=id)
For Each ctl In myControls
ctl.Enabled = Enab
Next ctl
End Sub

You may want to extend this to cover insert and delete. In which case the
relevant IDs for Column are 297 & 294 respctively and for Row they are 296 &
293

;-)

Shetty wrote in message
om...
Thnks for your suggession.
But, I need to do other operations by sheetchange event which
is not working with the protect and "UserInterfaceOnly"=true.
I intend to hide some rows with workbook open event and disable
users to insert/unhide any rows/columns. If user inserts/delets
any row/column, my entire programm gets disturbed and gives
unexpected and wrong results.

Thanks again.
Regards,
Shetty


"Loomah" wrote in message
...
Hi
Could you not protect the worksheet and set the "UserInterfaceOnly" to
true?
If not you may find this link useful
http://www.tek-tips.com/faqs.cfm?spid=707&sfid=4841

;-)


Shetty wrote in message
m...
I need to disable hide and unhide commands from the context
menu as well as from main menu for the users of one work book.
With this they should not be able to unhide rows or columns
which I have made hidden by a macro.
How to acheive it?
But at the same time, I want to use these commands programatically
from a macro befor save. Also this commands should be available
to user when they activate any other workbook.
Is it possible?
Regards,
Shetty

WOW, It works like a charm.
This is exactly what I wanted to do.
However, ID's 293,294,296,297 are working for only "delete".
Still "Insert " is enabled. What are the ID's for Insert?
Also if you dont mind, can you tell me how do you got these ID's
so that I can have a look at other commands also.
Thank you very much to spare time for me.
Regards,
Shetty.


"Loomah" wrote in message ...
Shetty
Use this code to disable/enable the row & column hide & unhide commands

Sub Disable()
Enable_Disable_Commands 883, False 'row hide
Enable_Disable_Commands 884, False 'row unhide
Enable_Disable_Commands 886, False 'col hide
Enable_Disable_Commands 887, False 'col unhide
End Sub

Sub Enable()
Enable_Disable_Commands 883, True 'row hide
Enable_Disable_Commands 884, True 'row unhide
Enable_Disable_Commands 886, True 'col hide
Enable_Disable_Commands 887, True 'col unhide
End Sub

Sub Enable_Disable_Commands(id As Integer, Enab As Boolean)
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, id:=id)
For Each ctl In myControls
ctl.Enabled = Enab
Next ctl
End Sub

You may want to extend this to cover insert and delete. In which case the
relevant IDs for Column are 297 & 294 respctively and for Row they are 296 &
293

;-)

Shetty wrote in message
om...
Thnks for your suggession.
But, I need to do other operations by sheetchange event which
is not working with the protect and "UserInterfaceOnly"=true.
I intend to hide some rows with workbook open event and disable
users to insert/unhide any rows/columns. If user inserts/delets
any row/column, my entire programm gets disturbed and gives
unexpected and wrong results.

Thanks again.
Regards,
Shetty


"Loomah" wrote in message
...
Hi
Could you not protect the worksheet and set the "UserInterfaceOnly" to
true?
If not you may find this link useful
http://www.tek-tips.com/faqs.cfm?spid=707&sfid=4841

;-)


Shetty wrote in message
m...
I need to disable hide and unhide commands from the context
menu as well as from main menu for the users of one work book.
With this they should not be able to unhide rows or columns
which I have made hidden by a macro.
How to acheive it?
But at the same time, I want to use these commands programatically
from a macro befor save. Also this commands should be available
to user when they activate any other workbook.
Is it possible?
Regards,
Shetty

Loomah,

Excellent piece of code - very useful. However I too found that ID no
293 and 294 work for enabling and disabling "delete row" and "delet
column" but 296 and 297 did not work for enabling and disabling "inser
row" and "insert column" despite finding the site below and seeing tha
ID nos 296 and 297 are mentioned

http://support.microsoft.com/default...b;EN-US;213552

Any thoughts?

Regards

Seamu

--
Message posted from http://www.ExcelForum.com

SOS,
How could you found it out? I have tried searching kb for but failed.
It's a whole list of ID's for all the command's and menu's and very much useful.
Thanks,
Shetty

SOS wrote in message ...
Loomah,

Excellent piece of code - very useful. However I too found that ID nos
293 and 294 work for enabling and disabling "delete row" and "delete
column" but 296 and 297 did not work for enabling and disabling "insert
row" and "insert column" despite finding the site below and seeing that
ID nos 296 and 297 are mentioned

http://support.microsoft.com/default...b;EN-US;213552

Any thoughts?

Regards

Seamus


---
Message posted from http://www.ExcelForum.com/

I have tried to use this code in the window activate and deactivate
events.
It gives error that the "object variable or with block variable not
set."
and halts at the line Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, id:=id)
When I hold the mouse over the mycontrols, a yellow pop up shows
mycontrols=nothing.
It was working perfectly fine if used as a saperate macro. But that
causes problem when user uses any other workbook where all the
commands should be available.

Any idea?
Regards,
Shetty.




SOS wrote in message ...
Loomah,

Excellent piece of code - very useful. However I too found that ID nos
293 and 294 work for enabling and disabling "delete row" and "delete
column" but 296 and 297 did not work for enabling and disabling "insert
row" and "insert column" despite finding the site below and seeing that
ID nos 296 and 297 are mentioned

http://support.microsoft.com/default...b;EN-US;213552

Any thoughts?

Regards

Seamus


---
Message posted from http://www.ExcelForum.com/

Sorry to post again.
But I have just discovered that even though the insert/delete command
is disabled, row/column can be inserted/deleted with the shortcut key
combinations i.e. ctrl++/ctrl+- (control plus "+" and control plus
"-"keys combinations)
How to disable it?
Regards,
Shetty.


"Loomah" wrote in message ...
Shetty
Use this code to disable/enable the row & column hide & unhide commands

Sub Disable()
Enable_Disable_Commands 883, False 'row hide
Enable_Disable_Commands 884, False 'row unhide
Enable_Disable_Commands 886, False 'col hide
Enable_Disable_Commands 887, False 'col unhide
End Sub

Sub Enable()
Enable_Disable_Commands 883, True 'row hide
Enable_Disable_Commands 884, True 'row unhide
Enable_Disable_Commands 886, True 'col hide
Enable_Disable_Commands 887, True 'col unhide
End Sub

Sub Enable_Disable_Commands(id As Integer, Enab As Boolean)
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, id:=id)
For Each ctl In myControls
ctl.Enabled = Enab
Next ctl
End Sub

You may want to extend this to cover insert and delete. In which case the
relevant IDs for Column are 297 & 294 respctively and for Row they are 296 &
293

;-)

Shetty wrote in message
om...
Thnks for your suggession.
But, I need to do other operations by sheetchange event which
is not working with the protect and "UserInterfaceOnly"=true.
I intend to hide some rows with workbook open event and disable
users to insert/unhide any rows/columns. If user inserts/delets
any row/column, my entire programm gets disturbed and gives
unexpected and wrong results.

Thanks again.
Regards,
Shetty


"Loomah" wrote in message
...
Hi
Could you not protect the worksheet and set the "UserInterfaceOnly" to
true?
If not you may find this link useful
http://www.tek-tips.com/faqs.cfm?spid=707&sfid=4841

;-)


Shetty wrote in message
m...
I need to disable hide and unhide commands from the context
menu as well as from main menu for the users of one work book.
With this they should not be able to unhide rows or columns
which I have made hidden by a macro.
How to acheive it?
But at the same time, I want to use these commands programatically
from a macro befor save. Also this commands should be available
to user when they activate any other workbook.
Is it possible?
Regards,
Shetty