disable ability to insert rows

[MikeL]

Hi, I am using XL 2000 and am trying to prevent users from choosing Insert-Rows from the toolbar menu for a specific workbook. I don't want to protect the workbook so I am trying to write code. What I have doesn't seem to work. Can someone correct?

This code is in ThisWorkbook

Sub DisableInsertRows()
With Application.CommandBars("Insert").Controls
.Item("Rows").Enabled = False
End With
End Sub

Thanks in advance,
MikeL

 

[Richie(UK)]

Hi Mike,

Try this:

Sub NoInsert()

With CommandBars("Worksheet Menu Bar")
        .Controls("Insert").Controls("Rows").Enabled = False
End With

With Application.CommandBars("Row")
        .Controls("Insert").Enabled = False
End With

End Sub

Delete the second one if you only want to delete the main menu item.

HTH

 

[MikeL]

Great macro Richie. It works so well that the Insert-Rows option is ghosted out in all my other workbooks even if I close out and reopen the Excel application. Would you mind modifying this code to work only in the workbook when I open it and then to enable Insert-Rows once the workbook is closed? I'm a newbie at writing code and haven't gotten to understand this part of code writing yet.

Thanks in advance,
MikeL

 

[MirantMan]

Here's what I did...

'=================================================
Private Sub Worksheet_Activate()
Dim objCmdBrPp As CommandBarPopup

Set objCmdBrPp = Application.CommandBars("Worksheet Menu Bar").Controls("Insert")
objCmdBrPp.Controls("Rows").Enabled = False
End Sub

'=================================================

Private Sub Worksheet_Deactivate()
Dim objCmdBrPp As CommandBarPopup

Set objCmdBrPp = Application.CommandBars("Worksheet Menu Bar").Controls("Insert")

objCmdBrPp.Controls("Rows").Enabled = True
End Sub

'==================================================

Hope that helps.

 

[Richie(UK)]

Hi Mike,

OK, try this:

1. Modify the routine above to accept a Boolean argument - like this:

Sub NoInsert(bSwitch As Boolean)

With CommandBars("Worksheet Menu Bar")
        .Controls("Insert").Controls("Rows").Enabled = bSwitch
End With

With Application.CommandBars("Row")
        .Controls("Insert").Enabled = bSwitch
End With

End Sub

2. Then add some event code to trigger the routine, in this case we're using the Open and BeforeClose events of the workbook. Double-click on the ThisWorkbook object in the Project window and then paste in the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    NoInsert (True)
End Sub

Private Sub Workbook_Open()
    NoInsert (False)
End Sub

HTH

 

[MikeL]

Thanks again, Richie.

I had to take out the piece below that you posted in part 1. Now it works perfectly.

With Application.CommandBars("Row")
.Controls("Insert").Enabled = bSwitch
End With

MikeL