Delete Row Question For Tom Ogilvy

[okrob]

Tom,
Back in '02 you wrote:
************************************
You can protect the worksheet, (tools=> Protection => Protect Sheet

But that will also protect any locked cells (the default setting is
locked).
It also disables formatting.


There is no setting to turn off just the adding and deleting of rows.


Regards,
Tom Ogilvy
************************************

As far as I know this is still true, but I was wondering if there were
a way (in Office2003) to disable the delete row selection from the
right click menu OR fire a msgbox as a substitution to the delete row
command? I have a need for this as well, but don't want to do
anything to Excel that I can't 'undo' as I close the file in question.
Rob

 

[Gary Brown]

The macro below will only disable the 'Hide' option under Format>Row.
I've also included (but commented out) the code for enabling, hiding and
unhiding the Hide/Unhide options under the Format>Row menu.

I've also included a macro for the right-click menu. The reason is that,
even though you've disabled the Row>Hide 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
' Format>Row>Hide, 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 Row>Hide option on the Cell menu
'Call DisableCellMenuItem


'enable the Row>Hide 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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[okrob]

The macro below will only disable the 'Hide' option under Format>Row.
I've also included (but commented out) the code for enabling, hiding and
unhiding the Hide/Unhide options under the Format>Row menu.

I've also included a macro for the right-click menu. The reason is that,
even though you've disabled the Row>Hide 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
' Format>Row>Hide, 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 Row>Hide option on the Cell menu
'Call DisableCellMenuItem

'enable the Row>Hide 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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.









- Show quoted text -

Thanks Gary... My biggest problem is that I have several worksheets
used by different departments that I roll into one sheet. The layouts
are identical for each department sheet, and I have a manpower
"totals" row. This row is linked to the rollup. If the department
decides to delete or insert a row, it throws my link off.
I think that I am just going to throw out the links and name the
ranges then update the total sheet by copying and pasting the ranges
programmatically. Eliminates the need for formulas altogether...
Rob