Worksheet_Activate macro not consistent across users

[mike.magill]

I have the following code in the Worksheet_Activate event. The part that should allow a user to activate the InsertRow and DeleteRow macros when right clicking on the row header does not work for some users.

Can anyone identify anything in the code which might not work across all Excel 2010 configurations?

Sub Worksheet_Activate()
' This macro changes the menu bar and contextual menus for Cells, Rows and Columns to limit
' and control the user's ability to interact with the spreadsheet.
            
    Call Set_Public_Variables
    
    Dim MenuBar As Object
    Dim ColumnBar As Object
    Dim ColumnBar2 As Object
    Dim CellBar As Object
    Dim CellBar2 As Object
    Dim RowBar As Object
    Dim RowBar2 As Object
    Dim cItem As Object
    Dim NewMenu As Object
    Dim MenuItem As Object


    If Right(ThisWorkbook.Name, 5) = ".xltm" Then Exit Sub
    
    Set CellBar = Application.CommandBars("Cell")
    Set CellBar2 = Application.CommandBars(Application.CommandBars("Cell").Index + 3)
    Set RowBar = Application.CommandBars("Row")
    Set RowBar2 = Application.CommandBars(Application.CommandBars("Row").Index + 3)
    Set ColumnBar = Application.CommandBars("Column")
    Set ColumnBar2 = Application.CommandBars(Application.CommandBars("Column").Index + 3)


    CellBar.Reset
    CellBar2.Reset
    RowBar.Reset
    RowBar2.Reset
    ColumnBar.Reset
    ColumnBar2.Reset


    CellBar.Enabled = True
    CellBar2.Enabled = True
    RowBar.Enabled = True
    RowBar2.Enabled = True
    ColumnBar.Enabled = False
    ColumnBar2.Enabled = False


    Call ResizeComments


    ' Restricts items on Cell Contextual Menu to Copy, Paste, Paste Special and Clear Contents
    For Each cItem In CellBar.Controls
        Select Case cItem.Caption
            Case "&Copy", "&Paste", "Paste &Special...", "Clear Co&ntents", "Insert Co&mment"
            Case Else
                cItem.Delete
        End Select
    Next cItem


    ' Restricts items on Cell Contextual Menu to Copy, Paste, Paste Special and Clear Contents
    For Each cItem In CellBar2.Controls
        Select Case cItem.Caption
            Case "&Copy", "&Paste", "Paste &Special...", "Clear Co&ntents", "Insert Co&mment"
            Case Else
                cItem.Delete
        End Select
    Next cItem


    ' If the document is being used as a single risk register....
    If Range("Consol_Marker").Value <> "Consolidation" Then
        
        ' Delete or disable all items on Row Contextual Menu
        For Each cItem In RowBar.Controls
            Select Case cItem.Caption
                Case "Cu&t", "&Copy", "&Paste", "Paste &Special...", "Clear Co&ntents", _
                     "&Format Cells...", "&Row Height", "&Hide", "&Unhide"
                    cItem.Enabled = False
                Case Else
                    cItem.Delete
            End Select
        Next cItem


        ' Delete or disable all items on Row Contextual Menu
        For Each cItem In RowBar2.Controls
            Select Case cItem.Caption
                Case "Cu&t", "&Copy", "&Paste", "Paste &Special...", "Clear Co&ntents", _
                     "&Format Cells...", "&Row Height", "&Hide", "&Unhide"
                    cItem.Enabled = False
                Case Else
                    cItem.Delete
            End Select
        Next cItem


        ' If the autofilter is not enabled add Insert Row and Delete Row macros
        If Worksheets("Risk Register").FilterMode = False Then
            With RowBar.Controls
                With .Add
                   .Caption = "&Insert"
                   .OnAction = "InsertRow"
                   .BeginGroup = True
                   .Move Before:=5
                End With
    
                With .Add
                   .Caption = "&Delete"
                   .OnAction = "DeleteRow"
                   .Move Before:=6
                End With
            End With
        End If
    Else
    
        ' If the document is being used to consolidate....
        ' Delete or disable all items on Row Contextual Menu
        For Each cItem In RowBar.Controls
            Select Case cItem.Caption
                Case "Cu&t", "&Copy", "&Paste", "Paste &Special...", "Clear Co&ntents", _
                     "&Format Cells...", "&Row Height", "&Hide", "&Unhide"
                    cItem.Enabled = False
                Case Else
            End Select
        Next cItem
    
        For Each cItem In RowBar2.Controls
            Select Case cItem.Caption
                Case "Cu&t", "&Copy", "&Paste", "Paste &Special...", "Clear Co&ntents", _
                     "&Format Cells...", "&Row Height", "&Hide", "&Unhide"
                    cItem.Enabled = False
                Case Else
            End Select
        Next cItem
    
    End If


    ' Reroute shortcut keys to relevant macros
    Application.OnKey "{F7}", "Spell_Check"
    Application.OnKey "^p", "Print_Options"
    Application.OnKey "^P", "Print_Options"
    Application.OnKey "^X", ""
    Application.OnKey "^x", ""


    'Disable Print and Print Preview buttons on standard menu bar
    Application.CommandBars("Standard").Controls(6).Enabled = False
    Application.CommandBars("Standard").Controls(7).Enabled = False
    Application.CommandBars("Standard").Controls(10).Enabled = False


End Sub

 

[mole999]

are they all set up to have macros run automatically

 

[mike.magill]

are they all set up to have macros run automatically

The users have been reminded to enable macros and in the two instances where users are having problems they have definitely got macros enabled.