Removing added commands from command bar when closing workbook

[rjheibel]

I added three commands to allow for inserting, deleting and removing rows in a protected file. While I was able to add the menu items correctly, I have not been able to correctly remove them when closing the workbook. The code I have thus far is below. Please let me know what I need to change to make the removal work. Thanks!

Coded into ThisWorkbook:

Private Sub Workbook_Open()
Application.Run "'sample File Name.xlsm'!addMenuItems"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Run "'Sample Excel File.xlsm'!removeMenuItems"
End Sub

Coded into Module:

Public Sub addMenuItems()
        With Application.CommandBars("row").Controls.Add(msoControlButton, , , 1, True)
                .Caption = "Delete protected row"
                .OnAction = "'Sample Excel File.xlsm'!deleteRow"
        End With
        With Application.CommandBars("row").Controls.Add(msoControlButton, , , 1, True)
                .Caption = "Insert protected row"
                .OnAction = "'Sample Excel File.xlsm'!insertProtectedRow"
        End With
        With Application.CommandBars("row").Controls.Add(msoControlButton, , , 1, True)
                .Caption = "Cut&Paste protected row"
                .OnAction = "'Sample Excel File.xlsm'!cutProtectedRow"
        End With
End Sub

Public Sub removeMenuItems()
  
    With Application.CommandBars("Worksheet Menu Bar")
        On Error Resume Next
        .Controls("Delete protected row").Delete
        On Error GoTo 0
    End With
    With Application.CommandBars("Worksheet Menu Bar")
        On Error Resume Next
        .Controls("Insert protected row").Delete
        On Error GoTo 0
    End With
    With Application.CommandBars("Worksheet Menu Bar")
        On Error Resume Next
        .Controls("Cut&Paste protected row").Delete
        On Error GoTo 0
    End With
End Sub

Public Sub deleteRow()

 Application.EnableCancelKey = xlDisabled
 Dim ws As Worksheet
 Dim selRows As Range, CBlanks As Range, IntersectedCells As Range
 
 On Error Resume Next
 Set ws = ActiveWorkbook.Sheets("LookAhead Schedule")
 Set selRows = Selection.EntireRow
 Set CBlanks = Columns("C").SpecialCells(xlBlanks)
 Set IntersectedCells = Intersect(selRows, CBlanks)
 
 On Error GoTo 0
     If ws.Name = ActiveWorkbook.ActiveSheet.Name Then
        If ActiveCell.row > 4 Then
            On Error Resume Next
            ActiveSheet.ShowAllData
              If Intersect(Columns("C"), selRows).Count <> IntersectedCells.Count Then
                MsgBox "One or More Rows Selected are P6 Activities" & vbLf & vbLf & "Operation cancelled!", vbCritical
              Else
                ActiveSheet.Unprotect "password"
                IntersectedCells.EntireRow.Delete
                ActiveSheet.Protect _
                    Password:="password", _
                    DrawingObjects:=True, _
                    Contents:=True, _
                    Scenarios:=True, _
                    AllowFiltering:=True
              End If
        Else
            MsgBox "Can not Delete Header Rows 1-4"
        End If
     Else
         MsgBox "Function Only Works in LookAhead Sheet!!!"
     End If
 Application.EnableCancelKey = xlInterrupt
End Sub

 

[Domenic]

Since you've added your controls to the "row" command bar, you'll need to refer to the "row" command bar when deleting them. However, your code can be re-written as follows . . .

Public Sub removeMenuItems()
 
    On Error Resume Next
    With Application.CommandBars("row")
        .Controls("Delete protected row").Delete
        .Controls("Insert protected row").Delete
        .Controls("Cut&Paste protected row").Delete
    End With
    On Error GoTo 0
    
End Sub

Hope this helps!

 

[Domenic]

By the way, if a user closes the workbook and clicks on Cancel when asked whether to save changes, the workbook will remain open but the the controls will have been deleted. So I would suggest that you amend your code to handle such situations. If you need help, start a new thread, and ask for help.

 

[rjheibel]

Domenic, This doesn't seem to be working for me. My issue is when I open the spreadsheet with the code to add the controls, and then open another spreadsheet, the controls can be viewed in the other spreadsheet. Then when I close the original spreadsheet with the code, the second spreadsheet still shows the added controls. Is there a way to only add the controls in the intended spreadsheet and not other spreadsheets opened at the same time, or a way to remove the controls from the other spreadsheets when closing?

 

[Domenic]

You can use the Worksheet_Activate() and Worksheet_Deactivate() event handlers instead.

The Worksheet_Activate() event handler gets triggered when you open your workbook or while another workbook is active and you activate your workbook.

The Worksheet_Deactivate() event handler gets triggered when you close your workbook or when you activate another workbook.

Note that there's no need for additional code to deal with situations where you close your workbook and click on Cancel when asked whether to save changes.

 

[RoryA]

If I were you I'd customise the Ribbon - then the controls will only be available for that workbook anyway.