Solved: Problems using protect and unprotect in VBA

[Rick Clark]

I have a spreadsheet which use a VBA to change data, color and font in a selection of locked cells. The rest of the cells are unlocked and selectable.

It currently does this by unprotecting the sheet clearing the data and then reprotecting the sheet. However when the sheet is closed and reopened the locked cells become selectable by the user.

I can fix it by opening with the following statement however it asks for a password to unprotect without being prompted to..
[VBA]With Worksheets("front page")
.EnableSelection = xlUnlockedCells
.EnableAutoFilter = True
.Protect Contents:=True, UserInterfaceOnly:=True
End With
[/VBA]
Is there an answer for either problem, it is important that the users do not have access to change the protected cells. I am up against a gun to get this done I would appreciate any help I can get. If it is easier my email is ricclark2000@yahoo.com Thank You

[Jan Karel Pieterse]

I would have some code in the workbook_Open event that protects all (applicable) worksheets with the keyword "UserInterfaceOnly:=True" as one of the arguments (note that Excel 2003 requires you to give the password in this code!):

Worksheets("Front page").Protect Password:="drowssap", UserInterfaceOnly:=True

After that, you don't need the unprotecting at all in your code.

[Rick Clark]

Preview Thanks for the help it solved the problem. In fact it made everything run smoother. Maybe you can answer one last question I have been searching for or direct me to the answer. I am using checkboxs in a form to select which way the formatting goes ie filtering. It works great however if the user is not finshed and exits and saves the workbook the checkboxes go back to the unchecked state. While this has no affect on what was filtered, if they go back to the form there is no referance to what they previously did and if they want to change it they have to check them all and uncheck them to make changes. Currently if you check a box it makes the selection and if you uncheck the box it removes the selection but only in that session.
Thanks Rick

[Jan Karel Pieterse]

Maybe by tying each checkbox to a cell on a (hidden) worksheet.
Thus if you save the file prior to exiting, the checkboxes will retain their values.

[Rick Clark]

Jan,

Thanks for you help, as for the check boxes I was looking for a way to do it thru code but gave up and used links to cells. I knew it would work that way and I am glad that you suggested it that way I know it was the right way. Thanks Rick

How do I mark this solved?

[Ken Puls]

Hi Rick,

The "Mark Solved" feature is currently undergoing "renovations". We're hoping that it will be fixed up within the next week. I'll modify the thread to mark it such for you though.

PS, Jan... Welcome to VBAX! Good to see you here!

[Jan Karel Pieterse]

Hi Ken,

<<PS, Jan... Welcome to VBAX! Good to see you here!>>

Yeah, I thought I'd drop by and admire the scenery a bit.