I have a workbook with 3 sheets in it. After the application runs it hides
a bunch of data on sheet1 and only displays the data on that sheet for a
particular enduser. What I want to disable is the Hide and unhide rows
feature for one. Is there an easy way of doing that and still not have to
worry about what happens initially when the macro runs?? Are there features
witin excel that allows to me shut off parts of the menu??

Hopefully I made myself clear as to what I am trying to acomplish and if not
let me know and I will see if I can phrase it differently.

Thanks in advance,

Les

Option Explicit

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/

To disable/enable columns change "ROW" to "COLUMN".

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Les" wrote:

I have a workbook with 3 sheets in it. After the application runs it hides
a bunch of data on sheet1 and only displays the data on that sheet for a
particular enduser. What I want to disable is the Hide and unhide rows
feature for one. Is there an easy way of doing that and still not have to
worry about what happens initially when the macro runs?? Are there features
witin excel that allows to me shut off parts of the menu??

Hopefully I made myself clear as to what I am trying to acomplish and if not
let me know and I will see if I can phrase it differently.

Thanks in advance,

Les

Hi Gary,

Listen I entered your code into my app but it didnt disable the Hide and
Unhide commands in the menu. I did a trace on the VB side and it does go
into the module and says it disables the 2 features but when I run the app I
am still able to hide and unhide rows ??? Do you or anyone out here know
why that is so ??

Look forward to hearing from you,

Thanks,

Les
"Gary Brown" wrote in
message ...
Option Explicit

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/

To disable/enable columns change "ROW" to "COLUMN".

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Les" wrote:

I have a workbook with 3 sheets in it. After the application runs it
hides
a bunch of data on sheet1 and only displays the data on that sheet for a
particular enduser. What I want to disable is the Hide and unhide rows
feature for one. Is there an easy way of doing that and still not have
to
worry about what happens initially when the macro runs?? Are there
features
witin excel that allows to me shut off parts of the menu??

Hopefully I made myself clear as to what I am trying to acomplish and if
not
let me know and I will see if I can phrase it differently.

Thanks in advance,

Les

Les,
Which menu are you looking at?
Which version of Excel are you using?

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Les" wrote:

Hi Gary,

Listen I entered your code into my app but it didnt disable the Hide and
Unhide commands in the menu. I did a trace on the VB side and it does go
into the module and says it disables the 2 features but when I run the app I
am still able to hide and unhide rows ??? Do you or anyone out here know
why that is so ??

Look forward to hearing from you,

Thanks,

Les
"Gary Brown" wrote in
message ...
Option Explicit

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/

To disable/enable columns change "ROW" to "COLUMN".

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Les" wrote:

I have a workbook with 3 sheets in it. After the application runs it
hides
a bunch of data on sheet1 and only displays the data on that sheet for a
particular enduser. What I want to disable is the Hide and unhide rows
feature for one. Is there an easy way of doing that and still not have
to
worry about what happens initially when the macro runs?? Are there
features
witin excel that allows to me shut off parts of the menu??

Hopefully I made myself clear as to what I am trying to acomplish and if
not
let me know and I will see if I can phrase it differently.

Thanks in advance,

Les

Hi Gary,

First thank you for keeping an eye on this post. The menu I am talking
about is the one that is in Exel located just underneath the title bar. If
you go to format in the menu and open it you will see ROWS and if you open
that up you will see the HIDE or UNHIDE commands. for your information when
I traced through the routine you sent me it does find the correct HIDE and
UNHIDE I believe but it just doesnt seem to be able to disable it even
through while debugging using the trace it appears to do it.??? The version
I am running Gary is Excel 97 but I have also duplicated this problem on
Excel 2000. Hope tis info helps you.

Thanks again Gary,

Les

"Gary Brown" wrote in
message ...
Les,
Which menu are you looking at?
Which version of Excel are you using?

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Les" wrote:

Hi Gary,

Listen I entered your code into my app but it didnt disable the Hide and
Unhide commands in the menu. I did a trace on the VB side and it does
go
into the module and says it disables the 2 features but when I run the
app I
am still able to hide and unhide rows ??? Do you or anyone out here
know
why that is so ??

Look forward to hearing from you,

Thanks,

Les
"Gary Brown" wrote
in
message ...
Option Explicit

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/

To disable/enable columns change "ROW" to "COLUMN".

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom
of
the
post.



"Les" wrote:

I have a workbook with 3 sheets in it. After the application runs
it
hides
a bunch of data on sheet1 and only displays the data on that sheet
for a
particular enduser. What I want to disable is the Hide and unhide
rows
feature for one. Is there an easy way of doing that and still not
have
to
worry about what happens initially when the macro runs?? Are there
features
witin excel that allows to me shut off parts of the menu??

Hopefully I made myself clear as to what I am trying to acomplish
and if
not
let me know and I will see if I can phrase it differently.

Thanks in advance,

Les

Sorry, I thought you were talking about disabling the 'Hide' on the
right-click menu.
Off hand, I don't know how to get further than 2 levels down in the menus,
so the following macro will disable FormatRow instead of just
FormatRowHide on the top menu.

'/===========================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object, objSubMenuItem As Object
Dim strMainMenuItem As String
Dim strSubMenuItem As String

'- - - - - - VARIABLES - - - - - - - - -
strMainMenuItem = "F&ormat" 'Main Menu Item
strSubMenuItem = "&Row" 'SubMenu Item
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("Worksheet Menu Bar").Controls
If objMenuItem.Caption = strMainMenuItem Then
For Each objSubMenuItem In objMenuItem.Controls
If objSubMenuItem.Caption = strSubMenuItem Then
objSubMenuItem.Enabled = False 'disable a menu item
' objSubMenuItem.Enabled = True 'enable a menu item
Exit For
End If
Next objSubMenuItem
Exit For
End If
Next objMenuItem

End Sub
'/===========================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Les" wrote:

Hi Gary,

First thank you for keeping an eye on this post. The menu I am talking
about is the one that is in Exel located just underneath the title bar. If
you go to format in the menu and open it you will see ROWS and if you open
that up you will see the HIDE or UNHIDE commands. for your information when
I traced through the routine you sent me it does find the correct HIDE and
UNHIDE I believe but it just doesnt seem to be able to disable it even
through while debugging using the trace it appears to do it.??? The version
I am running Gary is Excel 97 but I have also duplicated this problem on
Excel 2000. Hope tis info helps you.

Thanks again Gary,

Les

"Gary Brown" wrote in
message ...
Les,
Which menu are you looking at?
Which version of Excel are you using?

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Les" wrote:

Hi Gary,

Listen I entered your code into my app but it didnt disable the Hide and
Unhide commands in the menu. I did a trace on the VB side and it does
go
into the module and says it disables the 2 features but when I run the
app I
am still able to hide and unhide rows ??? Do you or anyone out here
know
why that is so ??

Look forward to hearing from you,

Thanks,

Les
"Gary Brown" wrote
in
message ...
Option Explicit

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
Exit For
End If
Next objMenuItem

End Sub
'/======================================/

To disable/enable columns change "ROW" to "COLUMN".

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom
of
the
post.



"Les" wrote:

I have a workbook with 3 sheets in it. After the application runs
it
hides
a bunch of data on sheet1 and only displays the data on that sheet
for a
particular enduser. What I want to disable is the Hide and unhide
rows
feature for one. Is there an easy way of doing that and still not
have
to
worry about what happens initially when the macro runs?? Are there
features
witin excel that allows to me shut off parts of the menu??

Hopefully I made myself clear as to what I am trying to acomplish
and if
not
let me know and I will see if I can phrase it differently.

Thanks in advance,

Les

objSubMenuItem.Enabled = False disables the menu option
objSubMenuItem.Enabled = True enables the menu option

Hi Gary,

I saw your last response briefly at work but when I got home it seems to
have been deleted from this thread ??? Would you mind sending it to me
again. Its the one where you say you know how to disable the rows but did
not know how to go to the third level. That will probably be fine for me.

Sorry for the inconvenience but have you ever seen a thread disappear like
that before??

Thanks Gary,

Les

"Gary Brown" wrote in
message ...
objSubMenuItem.Enabled = False disables the menu option
objSubMenuItem.Enabled = True enables the menu option

Here you go.

In this version, I also have an example of hiding the menu item as well as
disabling it.

'/===========================================/
Public Sub DisableMenuItem_Example()
'Disable / enable a menu item such as Sort, Data, etc
' - used when you don't want to let a user , for example,
' sort on their own
'05/31/2005
'
Dim objMenuItem As Object, objSubMenuItem As Object
Dim strMainMenuItem As String
Dim strSubMenuItem As String

'- - - - - - VARIABLES - - - - - - - - -
strMainMenuItem = "F&ormat" 'Main Menu Item
strSubMenuItem = "&Row" 'SubMenu Item
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("Worksheet Menu Bar").Controls
If objMenuItem.Caption = strMainMenuItem Then
For Each objSubMenuItem In objMenuItem.Controls
If objSubMenuItem.Caption = strSubMenuItem Then
objSubMenuItem.Enabled = False 'disable a menu item
' objSubMenuItem.Enabled = True 'enable a menu item
objSubMenuItem.Visible = False 'hide a menu item
' objSubMenuItem.Visible = True 'show a menu item
Exit For
End If
Next objSubMenuItem
Exit For
End If
Next objMenuItem

End Sub
'/===========================================/


--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Les" wrote:

Hi Gary,

I saw your last response briefly at work but when I got home it seems to
have been deleted from this thread ??? Would you mind sending it to me
again. Its the one where you say you know how to disable the rows but did
not know how to go to the third level. That will probably be fine for me.

Sorry for the inconvenience but have you ever seen a thread disappear like
that before??

Thanks Gary,

Les

"Gary Brown" wrote in
message ...
objSubMenuItem.Enabled = False disables the menu option
objSubMenuItem.Enabled = True enables the menu option

Took the time to look up the code for getting to the 3rd level.
The macro below will only disable the 'Hide' option under FormatRow.
I've also included (but commented out) the code for enabling, hiding and
unhiding the Hide/Unhide options.

You might want to incorporate my first posting (I've included it below in
case you no longer have access to it) into this macro. The reason is that,
even though you've disabled the RowHide option on the main menu, if the
client highlights the row and right clicks, guess what?!!, there's the 'Hide'
option!
I've included but commented out that ability also.

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable /hide / unhide a menu item such as
' FormatRowHide, etc
' - used when you don't want to let a user , for example,
' hide rows or columns
'
Dim objMenuItem As Object
Dim objSubMenuItem As Object
Dim objSub3MenuItem As Object
Dim strMainMenuItem As String
Dim strSubMenuItem As String
Dim strSub3MenuItem_Hide As String
Dim strSub3MenuItem_Unhide As String

'- - - - - - VARIABLES - - - - - - - - -
strMainMenuItem = "F&ormat" 'Main Menu Item
strSubMenuItem = "&Row" 'SubMenu Item
'&Column
strSub3MenuItem_Hide = "&Hide" ' 3rd Level Menu Item
strSub3MenuItem_Unhide = "&Unhide" ' 3rd Level Menu Item
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In _
CommandBars("Worksheet Menu Bar").Controls
If objMenuItem.Caption = strMainMenuItem Then
For Each objSubMenuItem In objMenuItem.Controls
If objSubMenuItem.Caption = strSubMenuItem Then
For Each objSub3MenuItem In objSubMenuItem.Controls
If objSub3MenuItem.Caption = _
strSub3MenuItem_Hide Or _
objSub3MenuItem.Caption = _
strSub3MenuItem_Unhide Then
'disable a menu item
objSub3MenuItem.Enabled = False
'enable a menu item
objSub3MenuItem.Enabled = True
'hide a menu item
objSub3MenuItem.Visible = False
'show a menu item
objSub3MenuItem.Visible = True
'disable the RowHide option on the Cell menu
Call DisableCellMenuItem
'enable the RowHide option on the Cell menu
Call EnableCellMenuItem
End If
Next objSub3MenuItem
End If
Next objSubMenuItem
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub DisableCellMenuItem()
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableCellMenuItem()
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
End If
Next objMenuItem

End Sub
'/======================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Les" wrote:

Hi Gary,

I saw your last response briefly at work but when I got home it seems to
have been deleted from this thread ??? Would you mind sending it to me
again. Its the one where you say you know how to disable the rows but did
not know how to go to the third level. That will probably be fine for me.

Sorry for the inconvenience but have you ever seen a thread disappear like
that before??

Thanks Gary,

Les

"Gary Brown" wrote in
message ...
objSubMenuItem.Enabled = False disables the menu option
objSubMenuItem.Enabled = True enables the menu option

Hi Gary,

Just a quick hello and thank you for the code snippet. I have not had time
to implement it but plan on doing so sometime this week. If you can check
here a few times to look for my feedback I would appreciate it.

Big thanks,

Les

"Gary Brown" wrote in
message ...
Took the time to look up the code for getting to the 3rd level.
The macro below will only disable the 'Hide' option under FormatRow.
I've also included (but commented out) the code for enabling, hiding and
unhiding the Hide/Unhide options.

You might want to incorporate my first posting (I've included it below in
case you no longer have access to it) into this macro. The reason is
that,
even though you've disabled the RowHide option on the main menu, if the
client highlights the row and right clicks, guess what?!!, there's the
'Hide'
option!
I've included but commented out that ability also.

'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable /hide / unhide a menu item such as
' FormatRowHide, etc
' - used when you don't want to let a user , for example,
' hide rows or columns
'
Dim objMenuItem As Object
Dim objSubMenuItem As Object
Dim objSub3MenuItem As Object
Dim strMainMenuItem As String
Dim strSubMenuItem As String
Dim strSub3MenuItem_Hide As String
Dim strSub3MenuItem_Unhide As String

'- - - - - - VARIABLES - - - - - - - - -
strMainMenuItem = "F&ormat" 'Main Menu Item
strSubMenuItem = "&Row" 'SubMenu Item
'&Column
strSub3MenuItem_Hide = "&Hide" ' 3rd Level Menu Item
strSub3MenuItem_Unhide = "&Unhide" ' 3rd Level Menu Item
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In _
CommandBars("Worksheet Menu Bar").Controls
If objMenuItem.Caption = strMainMenuItem Then
For Each objSubMenuItem In objMenuItem.Controls
If objSubMenuItem.Caption = strSubMenuItem Then
For Each objSub3MenuItem In objSubMenuItem.Controls
If objSub3MenuItem.Caption = _
strSub3MenuItem_Hide Or _
objSub3MenuItem.Caption = _
strSub3MenuItem_Unhide Then
'disable a menu item
objSub3MenuItem.Enabled = False
'enable a menu item
objSub3MenuItem.Enabled = True
'hide a menu item
objSub3MenuItem.Visible = False
'show a menu item
objSub3MenuItem.Visible = True
'disable the RowHide option on the Cell menu
Call DisableCellMenuItem
'enable the RowHide option on the Cell menu
Call EnableCellMenuItem
End If
Next objSub3MenuItem
End If
Next objSubMenuItem
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub DisableCellMenuItem()
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableCellMenuItem()
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
End If
Next objMenuItem

End Sub
'/======================================/

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Les" wrote:

Hi Gary,

I saw your last response briefly at work but when I got home it seems to
have been deleted from this thread ??? Would you mind sending it to me
again. Its the one where you say you know how to disable the rows but
did
not know how to go to the third level. That will probably be fine for
me.

Sorry for the inconvenience but have you ever seen a thread disappear
like
that before??

Thanks Gary,

Les

"Gary Brown" wrote
in
message ...
objSubMenuItem.Enabled = False disables the menu option
objSubMenuItem.Enabled = True enables the menu option

OOPS! Forgot to comment out as I said I would.
This fixes that :O
FYI, whenever you post to this thread, I have set it up to get notified.
--
Gary Brown



'/======================================/
Public Sub DisableMenuItem_Example()
'Disable / enable /hide / unhide a menu item such as
' FormatRowHide, etc
' - used when you don't want to let a user , for example,
' hide rows or columns
'
Dim objMenuItem As Object
Dim objSubMenuItem As Object
Dim objSub3MenuItem As Object
Dim strMainMenuItem As String
Dim strSubMenuItem As String
Dim strSub3MenuItem_Hide As String
Dim strSub3MenuItem_Unhide As String

'- - - - - - VARIABLES - - - - - - - - -
strMainMenuItem = "F&ormat" 'Main Menu Item
strSubMenuItem = "&Row" 'SubMenu Item
'&Column
strSub3MenuItem_Hide = "&Hide" ' 3rd Level Menu Item
strSub3MenuItem_Unhide = "&Unhide" ' 3rd Level Menu Item
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In _
CommandBars("Worksheet Menu Bar").Controls
If objMenuItem.Caption = strMainMenuItem Then
For Each objSubMenuItem In objMenuItem.Controls
If objSubMenuItem.Caption = strSubMenuItem Then
For Each objSub3MenuItem In objSubMenuItem.Controls
If objSub3MenuItem.Caption = _
strSub3MenuItem_Hide Or _
objSub3MenuItem.Caption = _
strSub3MenuItem_Unhide Then

'disable a menu item
objSub3MenuItem.Enabled = False

'enable a menu item
'objSub3MenuItem.Enabled = True

'hide a menu item
'objSub3MenuItem.Visible = False

'show a menu item
'objSub3MenuItem.Visible = True

'disable the RowHide option on the Cell menu
'Call DisableCellMenuItem

'enable the RowHide option on the Cell menu
'Call EnableCellMenuItem

End If
Next objSub3MenuItem
End If
Next objSubMenuItem
Exit For
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub DisableCellMenuItem()
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = False 'disable a menu item
End If
Next objMenuItem

End Sub
'/======================================/
Public Sub EnableCellMenuItem()
'
Dim objMenuItem As Object
Dim strHide As String, strUnhide As String

'- - - - - - VARIABLES - - - - - - - - -
strHide = "&Hide" 'item #1 to find
strUnhide = "&Unhide" 'item #1 to find
'- - - - - - - - - - - - - - - - - - - -

For Each objMenuItem In CommandBars("ROW").Controls
If objMenuItem.Caption = strHide Or _
objMenuItem.Caption = strUnhide Then
objMenuItem.Enabled = True 'enable a menu item
End If
Next objMenuItem

End Sub
'/======================================/