Help: How to lock sheet but still let macro edit cell

[yurble_vn]

Dear All,

Please help in followings:
I intend to lock a sheet where:

Lock Sheet but allow some editable cells as input (done)
There is a chart, locked.

Macro still run, use the input from un-locked cells to calculate then output from macro will be wroten in some locked cells(need help)
Macro activated through check box, slide bar which must still run and adjustable: user can check in check box, can drag slide bar... (need help)
All change from above can effect to a chart in the locked sheet. (need help)Thanks in advance

[figment]

i would go through and record the options you use to lock the sheet
then have th macro unprotect the sheet when it starts, and reprotect the sheet when it finishes.

[Simon Lloyd]

Hi, locking a cell relies on the worksheet being protected, so simply removing the protection before running your code and then re-applying the protection after will suffice.


Activesheet.Unprotect Password="password

'Run your code

Activesheet.Protect Password ="password"

[mikerickson]

Use the UserInterfaceOnly property

ActiveSheet.Protect UserInterfaceOnly:=Truewill protect the sheet from only the User Interface (keyboard & mouse) but will allow VB to change the sheet.

The property will remain True until either set False by your code or the workbook is closed. When re-opened UserInterfaceOnly will have been reset to the default False.

[yurble_vn]

HI Simon,

When using protect, it always return an alert, is there any way to get the alert disappeared?

Hi Mike,

Could you give more details example? Sorry for so many requirements. BUt I'm not so good at vba

Thanks all for supporting :kiss

[Simon Lloyd]

Use this before Unprotect

Application.DisplayAlerts = False
and this one after protect

Application.DisplayAlerts = True

[mikerickson]

Put this in the ThisWorkbook code module. Save, close, re-open the workbook. Now, everytime the workbook is opened, your VB code will be able to change any sheet, even if it is protected, but the user will not be able to change protected sheets. The only potential problem is that if the workbook is closed with a password protected sheet, the user will be prompted for that password when the sheet is re-opened.
(There are ways around that.)

Private Sub Workbook_Open()
Dim xSheet As Worksheet
For Each xSheet In ThisWorkbook.Sheets
With xSheet
.Protect _
DrawingObjects:=.ProtectDrawingObjects, _
Contents:=.ProtectContents, _
Scenarios:=.ProtectScenarios, _
UserInterFaceOnly:=True
End With
Next xSheet
End Sub

[RECrerar]

Hi Mike, I am trying to do the same as yurble_vn. i have used your code and it works to initially protect the sheet and still sllow entries to be made via the userform. However when the macro is complete the worksheets are no longer protected. Do you know why this might be?

[RECrerar]

Hey, not sure what was happening, but have just tested it again and seems to be working fine, thanks

[mikerickson]

Closing the workbook, resets UserInterfaceOnly to False. The Workbook_Open routine I posted sets the UIO=True but doesn't change protection.

It's a great technique for non-password protection. User chosen passwords can be a problem. BUT...other than protecting data from clumbsy typing, Excel's protection is unreliable, so the defect is small.

[mperrah]

Can you specifiy cells to block userinterface, instead of the whole sheet.
Or do you pick the properties cell by cell as locked or unlocked
Then locking the sheet uses the locked cells to block userinterface?
how do you add the userinterface to the sheet protect command?

example:
worksheets("sheet1").range("C6:C25").protect UserInterFaceOnly:=True ' like this?

[unmarkedhelicopter]

No, it's a global thing (and that includes the US !)

[mikerickson]

A sheet is the smallest item that can have its UserInterfaceOnly set.
I have trouble imagning a scenario where it would be useful for only some cells.

(But I also have trouble understanding why it isn't the default setting for protection.)

[mperrah]

I got it to work, my error.
I had not set the protection lock settings for the cells individualy.
All were locked.
Once I set each cell to locked or onlocked,
protecting the sheet with the userinterfaceonly worked like a charm.
Mark

[mikerickson]

Glad to have helped.