Workbook protection

  • Thread starter Thread starter mushy_peas
  • Start date Start date
M

mushy_peas

i want to protect a cell so it can't be changed by a person, but an
formulas or macros CAN.

I know how to protect a sheet, but it stops macros too.

Thanks
 
mushy_peas,

You have two options.....

Worksheets("yoursheetname").Protect Password:="yourpassword", _
UserInterfaceOnly:=True

The above needs to be in the Workbook_Open event
or a sub named Auto_Open. It will protect manual entries in
protected cells but allow macros to manipulate them.

or................

Worksheets("yoursheetname").Unprotect Password:="yourpassword"
' do what you want
Worksheets("yoursheetname").Protect Password:="yourpassword"

John
 
If you protect your worksheet in code, you can grant your code a special
dispensation.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

That said, worksheet protection is very weak. There's code posted here every
day/week that would unprotect the worksheet.
 
Hey Mushy,

I eat my peas with honey
I've done it all my life
It makes the peas taste funny
But they don't roll off my knife.

ba-dap
 
thanks, but i dont think i was clear.

I have a sheet in whick only 1 cell that needs to be protected bu
macros allowed to increment it.

The rest of the sheet is not protected.

So what i have done is locked that one cell. and set everything els
not locked. then Protected the sheet. But stops Macros from changin
that one cell
 
This version:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

Would go into a General module.

(don't forget to adjust the worksheet name and password.)

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Don't forget to lock the VBA Project, too. Else you'll have inquisitive types
looking at your code and seeing the password.

Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.
 
mushy_peas,

I also ask your similar question before. Dave Peterson provided good
answer to me. You may try the following code that I have used. Most
part of code was extracted from Dave Peterson reply.

Assume you have Worksheet "sheet1" and Cell "A1" need to increment.

Option Explicit
Private Sub Workbook_Open()

'Unprotecting worksheet
Worksheets("sheet1").Unprotect Password:="hi"

'Value incrementing
With Worksheets("sheet1").Range("A1")
If IsNumeric(.Value) Then
..Value = .Value + 1
Else
MsgBox "It Cannot Increment Cell"
End If
End With

'Protect Worksheet
With Worksheets("sheet1")
..Protect Password:="hi", userinterfaceonly:=True
End With


End Sub

Remember Dave Suggestion:
(1) the value isn't really incremented for next time until you save the
worksheet.
(2) Don't forget to lock the VBA Project, too.
 
i dont understand this business about resetting. It works first time
round, then i cant change anything. why?
 
mushy_peas,
i dont understand this business about resetting.
Click to expand...
This line:
Protect Password:="hi", userinterfaceonly:=True
The protect password will "stick" but the "UserInterfaceOnly"
will not.

The "UserInterfaceOnly" has to be set via code.
If you unprotect the sheet and reprotect it without the code
it won't work. If you save, close and reopen the workbook,
that setting won't still be there.

sooooooo.....

In the workbook_open event, set the UserInterfaceOnly property.
Anytime in your code that you unprotect the sheet (or do it
manually), you'll have to run that line of code again.

John
 
Ohhhh i get it.

So could have a macro:-
Sub protect()
With Worksheets("sheet1")
Protect Password:="hi", userinterfaceonly:=True
End Sub

to get the same protection again, yes??
 
By George, I think you've got it! <vbg>.

mushy_peas < said:
Ohhhh i get it.

So could have a macro:-
Sub protect()
With Worksheets("sheet1")
Protect Password:="hi", userinterfaceonly:=True
End Sub

to get the same protection again, yes??
Click to expand...
 
Hey, im getting good at this. :O)
but i hate boy george!!!!!!!!

how long b4 i get as good as you guys?
 
mushy_peas,
how long b4 i get as good as you guys??
Click to expand...
I've been working at it for a few years now.
I'm still not even getting close to some of "these guys"

Keep at it though. These ng's are the absolute best place to learn.

John
 
yeah, i know what you mean. i feel a right noob sometimes. dont think
will even come close to some of the guys, but hey its fun trying

though this forum is the best. ppl are are helpful here and the th
replys come thick and fast
 
Mushy,

The UserInterfaceOnly doesn't always work. It's documented in the microsoft
knowledge base. The workaround is to unprotect the sheet, change your
stuff, and then reprotect.
 
Back
Top