Macros in a protected sheet

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
I have a worksheet with a mixture of data entry cells and cells derived from formulas.

I want to protect the formula derived cells so I have locked them and protected the worksheet (no password).

I have various buttons running macros to perform bespoke filters. These are inoperative because the worksheet is protected.

Is there a way to allow the macros to run but still keep the worksheet protected so that users cannot overwrite cells with formulas?

Many thanks

HT
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,763
Office Version
365
Platform
Windows
Put
Code:
Sheets("sheet1").Unprotect
at the start of the code &
Code:
Sheets("sheet1").Protect
at the end, changing sheet name to suit.
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Many thanks, and apologies for late reply (been off to buy a dog!)

I tried this and now even with the worksheet unprotected, none of the buttons work. If I create a new button and assign a macro to it that doesn't work either!

Help!

HughT
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,763
Office Version
365
Platform
Windows
Can you run the macros manually?
Also what happens when you try to run a macro from a button?
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Macros run manually, but the buttons are disabled, so that I cannot even select them to format them etc when the worksheet is unlocked. If I create a new button and assign a macro to it, it still doesn't work, so presumably it is something to do with buttons permissions as opposed to the macro itself.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,763
Office Version
365
Platform
Windows
Are you using Form Control Buttons, or ActiveX?
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
A shape with a macro assigned to it - not sure which that is, sorry!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,763
Office Version
365
Platform
Windows
That would be Form Control.
I cannot think of any reason why you would get that. Have you tried re-booting your computer since this first happened?
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
107
Office Version
365
Platform
Windows
Ah, the old ones are the best ones!

I haven't and can't at the moment, but I will give it try later.

Many thanks for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,763
Office Version
365
Platform
Windows
Let me know how it goes, another possibility is the workbook has become corrupted.
Is it just the one workbook that has problems?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,211
Messages
5,467,290
Members
406,531
Latest member
Hazem Wageh

This Week's Hot Topics

Top