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