disabling macros when closing WB

[orbatrin]

I am working on a small project that is complete but when i close the workbook and open a new workbook, my paste special/values macro that overrides the paste in the right click menu is still active. How do I disable all macros automatically upon closing workbook or how to disable specific macros auto.
here is the code for the paste special/values used on auto open and also a module.

Sub Auto_Open()


    Application.CommandBars("Cell").Reset
        With Application.CommandBars("Cell")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click cell paste to use Pastevalues macro
            
        End With

    Application.CommandBars("Row").Reset
        With Application.CommandBars("Row")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click row paste to use Pastevalues macro
            
        End With

    Application.CommandBars("Column").Reset
        With Application.CommandBars("Column")
            .Controls("Paste").OnAction = "PasteValues" ' Changes right click column paste to use Pastevalues macro
            
        End With

    Application.CommandBars("Worksheet Menu Bar").Reset
    With Application.CommandBars("Worksheet Menu Bar").Controls
        .Item(2).Controls(6).OnAction = "PasteValues"  ' changes edit/paste to use Pastevalues macro
        .Item(2).Controls(7).Enabled = False ' disable paste special
    End With

    Application.CommandBars("Standard").Reset
    With Application.CommandBars("Standard").Controls.Item(12)
        .Enabled = False
    End With
    If Workbook_Deactivate Then End Sub
End Sub

Sub pastevalues()
On Error Resume Next
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

This is what I have in ThisWorkBook to disable/enable cut, delete and cellDragDrop.

Private Sub Workbook_Activate()
    Dim ctrl As Office.CommandBarControl
    For Each ctrl In Application.CommandBars.FindControls(ID:=21) 'Disable Cut
        ctrl.Enabled = False
    Next ctrl
        For Each ctrl In Application.CommandBars.FindControls(ID:=292) 'Disable Delete
            ctrl.Enabled = False
        Next
        Application.CellDragAndDrop = False
        
End Sub
Private Sub Workbook_Deactivate()
    Dim ctrl As Office.CommandBarControl
    For Each ctrl In Application.CommandBars.FindControls(ID:=21) 'Enable Cut
        ctrl.Enabled = True
    Next ctrl
        For Each ctrl In Application.CommandBars.FindControls(ID:=292) 'Enable Delete
            ctrl.Enabled = True
        Next
        Application.CellDragAndDrop = True
        
End Sub

Thanks for the help.

[davesexcel]

I would suppose you would need a workbook_close event that resets everything when you close the workbook.

[royUK]

You really should not mess with user settings with your code, it can lead to many problems as you are beginning to find out.

As Dave says try putting your restore code into the workbook close event as well as de-activate

[orbatrin]

Thank you for your response. I am aware that changing user settings could lead to problems but this is the only way I can make it user friendly.

Is there a code to reset all to default? or can someone walk me through how to reverse my paste special code so that I may add to my deactivate sub or close event. All the code is listed.

[royUK]

Thank you for your response. I am aware that changing user settings could lead to problems but this is the only way I can make it user friendly.

How does changing excelmake it more user friendly. You really should not do this unless your Excel VBA skills are above basic.

This example contains allthe code to remove cut,copy & paste & restore it

[wotadude]

Hi RoyUK,

Is it possible to re-assign paste functionality/shortcut/key to pastespecial xlvalues.
I often have a problem where users paste in data, and corrupt cell formatting and validation.

Any help would be appreciated.
Thanks.

[orbatrin]

royUK,

Thank you for the sample code that you posted, gives me an idea of more possibilities to activate and deactivate macros. Unfortunately the code does not look like it will override my paste special macros. that code is good to disable and enable cut, copy, and paste.

Just an idea...is it possible to reassign I.D's with a different control I.D and then reverse it at Workbook_Deactivate?? this would be a more simplified way to reassign Paste with Paste Special/values.

WotaDude is correct. Users are cutting/copying formulas and corrupting data..there has to be a more simplified way to override controls in specified workbooks. Any help would be greatly appreciated.