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

 

[JLGWhiz]

You can probably remove the macro recorder button from the ribbon by using the modify ribbon option when right clicking on the ribbon. But as a side comment, when you make a file so idiot proof that it requires strict file maintenance and dicipline it can lead to future problems if the designer and maintainer leaves the company without ever training anyone else on how to use Excel. It has happened many times in many companies. My personal theory has been to educate the users rather than limit their ability to learn. But, that is just my personal policy.

 

[Kenneth Hobson]

Did you protect the workbook too?

 

[Fluff]

Even if it's possible to prevent somebody from accessing the recorder in (say) Book1.xlsm, what's to stop them from starting the recorder in Book2.xlsm & then switching to Book1?

 

[Fluff]

Thinking about it, if you have locked the VBA project, then you will need to supply a password inorder to record a macro in that workbook.
Although it could still saved in your Personal.xlsb.

 

[Joe4]

Welcome to the Board!

It sounds to me like maybe you want to give them access to an entry form, instead of to the spreadsheet itself.
Maybe lock everything down, force them to enter all data via an entry form, and have VBA write the data from the entry form to the spreadsheet.
In that case, even if they were able to try to record a macro, they wouldn't be able to do much with it since most everything is locked down (so recording a macro wouldn't do much good).

 

[SQELady]

You can probably remove the macro recorder button from the ribbon by using the modify ribbon option when right clicking on the ribbon. But as a side comment, when you make a file so idiot proof that it requires strict file maintenance and dicipline it can lead to future problems if the designer and maintainer leaves the company without ever training anyone else on how to use Excel. It has happened many times in many companies. My personal theory has been to educate the users rather than limit their ability to learn. But, that is just my personal policy.

@JLGWhiz - Thank you for your input. Modifying the ribbon in the way you suggest is specific to the local system, and would not carry over to a spreadsheet opened up on someone else's system - so that is not an option.

I work in a medical device regulated environment with many expectations around spreadsheet validation. I figure by validating a spreadsheet template that will only save text and not allow macros, I can make the case that spreadsheets built with that template will not require assessment for validation requirements - and therefore not be subject to follow-on validation needs. The main purpose of this particular template would be to only allow input of text for communicating supporting data only. The users still have options to create other spreadsheets outside of this template, but then would be subject to assessment for validation needs.

 

[SQELady]

Thinking about it, if you have locked the VBA project, then you will need to supply a password inorder to record a macro in that workbook.
Although it could still saved in your Personal.xlsb.

@Fluff - thank you for your input. I did lock down the VBA project with a password (as well as the workbook structure, and each individual worksheet), but there was no restriction on the capability to record a macro.

 

[SQELady]

You can probably remove the macro recorder button from the ribbon by using the modify ribbon option when right clicking on the ribbon. But as a side comment, when you make a file so idiot proof that it requires strict file maintenance and dicipline it can lead to future problems if the designer and maintainer leaves the company without ever training anyone else on how to use Excel. It has happened many times in many companies. My personal theory has been to educate the users rather than limit their ability to learn. But, that is just my personal policy.

@JLGWhiz - Good input, and I agree. Here is a little background - I work in a medical device regulated environment with many expectations around spreadsheet validation. I figure by validating a spreadsheet template that will only save text and not allow macros, I can make the case that spreadsheets built with that template will not require assessment for validation requirements - and therefore not be subject to follow-on validation needs. The main purpose of this particular template would be to only allow input of text for communicating supporting data only. The users still have options to create other spreadsheets outside of this template, but then would be subject to assessment for validation needs.

 

[Kenneth Hobson]

And?

Did you protect the workbook too?