How to disable macro recording at a workbook level

[SQELady]

Hi there,

I'm working on developing a spreadsheet that locks down all data entered to text only - and contains no formulas, and will not allow recording of macros. So I locked down the format of the worksheets to text, and the structure of the spreadsheet to not add more worksheets, and restricted all pasted in text to paste in values only (via VBA). And I locked the VBA project with a password. Now I'm challenged with how to restrict creations of new macros - it still lets me record a new macro, regardless of the restrictions I have in place.

All the searching I am doing has it being done through the "Trust Center" - but that is system related, and I only want it locked down in respect to the spreadsheet. Ideally, I would like to disable all the ways that a user may access the "Record a macros" function.

Any thoughts?

Best regards,
SQELady

 

[SQELady]

And?

Yes, each worksheet is individually protected.
Workbook structure has been protected.
VBA project has been protected.

 

[Fluff]

If I understand you correctly, preventing someone recording a macro is not the problem. The problem is to stop someone running a non-approved macro on the template.
No matter what provisions and safeguards you put in place, someone with the right knowledge (or the ability to search the net) will be able to bypass them.

 

[Kenneth Hobson]

As was discussed earlier, you can stop saving the recording to that workbook by locking the View in the VBAProject unless the password is entered. They can still record to another workbook. Howsoever, if they do not have the password to save the file if you added one, or the password to the sheet if you added one, it will only work on the unlocked cells or such.

I normally protect sheets in the workbook open event using option UserInterfaceOnly=True. This allows its workbook macros to make changes. I have not tested it to see if it would allow an outside macro to make changes without the password. If it allowed it, one could code the macros using sheets Unprotect, code, Protect.

As was also discussed too: workbook, worksheet, and VBAProject passwords only provide a modicum of protection. It basically keeps the honest, honest, and prevents "honest" mistakes.

 

[JLGWhiz]

I repeat, educating the user is the best method of worksheet integrity. Or you could use threats of discharge if they are untrainable.(joking, of course). I think that securing the access to the code modules is probably sufficient to protect the sheet from anyone trying use a macro that is developed in that file. But as pointed out previously, it will not stop a knowledgeable person from running a macro on the file if they are intent on doing so. You might be beating the proverbial dead horse here.

 

[Jaafar Tribak]

How about Removing the Developper Tab or disabling the Record Macro control via xml for your specific workbook?

 

[Jaafar Tribak]

A cheap workaround that should immediatly abort the recording of macros while your workbook is open :

In the ThisWorkbook Module:

Option Explicit

Private WithEvents cmbrs As CommandBars


Private Sub Workbook_Open()
    Set cmbrs = Application.CommandBars
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmbrs = Application.CommandBars
End Sub


Private Sub cmbrs_OnUpdate()
    If Application.CommandBars.GetLabelMso("MacroRecord") = "Stop Recording" Then
        Application.CommandBars.ExecuteMso ("MacroRecord")
        Debug.Print "Macro Recording aborted!"
    End If
End Sub