How to disable macro recording at a workbook level

SQELady

New Member
Joined
Sep 12, 2019
Messages
5
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
 

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.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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).
 
Last edited:

SQELady

New Member
Joined
Sep 12, 2019
Messages
5

ADVERTISEMENT

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

New Member
Joined
Sep 12, 2019
Messages
5
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

New Member
Joined
Sep 12, 2019
Messages
5
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.
 

Forum statistics

Threads
1,136,317
Messages
5,675,047
Members
419,546
Latest member
RobWayCot

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
Top