Removing added commands from command bar when closing workbook

rjheibel

New Member
Joined
Mar 8, 2018
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:

Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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 . . .

VBA Code:
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
If I were you I'd customise the Ribbon - then the controls will only be available for that workbook anyway.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top