Macros in a protected sheet

HughT

Board Regular
Joined
Jan 6, 2012
Messages
87
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
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
87
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
30,213
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
87
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
30,213
Office Version
365
Platform
Windows
Are you using Form Control Buttons, or ActiveX?
 

HughT

Board Regular
Joined
Jan 6, 2012
Messages
87
A shape with a macro assigned to it - not sure which that is, sorry!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
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
87
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
30,213
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?
 

Forum statistics

Threads
1,077,795
Messages
5,336,373
Members
399,077
Latest member
johnk94

Some videos you may like

This Week's Hot Topics

Top