How to lock specific cells in VBA but leave them editable by code

0
votes

I have some cells that are filled by some formula created in VBA script. I want to make them not-editable (locked) by user input but let the script fill them with formulas.

I tried by protecting worksheet with a password and then : 

Range.locked = True

But this didn't allow the code to edit the cells..

2
excelvbaexcel-formula
I Think you might read this, once you tried it if it still doesn't work please edit your issue. – Dorian
How are you populating the cells in code? Using cells(x,y)= value? – Blingers
In the spirit of IT... Have you tried turning it off and back on again? This would actually work for your code, to set =false at the start of your code, then =true as the last step in your code. – Cyril
@Blingers I use this : myRow.Offset(0, 1).FormulaArray = variableThatContainsFormulaString – Lomoo
@Cyril i will try – Lomoo

2 Answers

2
votes

Protect the sheet with UserInterfaceOnly parameter:

MyWorksheet.Protect "Password", UserInterfaceOnly := True

where MyWorksheet is a valid reference of your worksheet object

1
votes

Try

ActiveSheet.Unprotect
myRow.Offset(0, 1).FormulaArray = variableThatContainsFormulaString 
ActiveSheet.Protect

Obvs prob better replacing active sheet with your actual sheet but you get the idea.

Looks like this has the available solutions though... Protecting cells in Excel but allow these to be modified by VBA script