Checkbox for protection

  • Thread starter Thread starter lilbpaw
  • Start date Start date
L

lilbpaw

I am new to excel, but have created a sheet that will allow a user t
enter the time they start work, leave for lunch, return from lunch an
clock out to go home along with a couple other functions.
What I would like, and I saw on another sheet is a check box that whe
checked will lock certain cells and hide the formula so the user ca
not type in them.
I have searched and read everywhere but can not seem to find the righ
'command' or coding.
Can someone please point me in the right direction.

Thanks,
lilbpa
 
I can creat a simple macro and i can add the check box from the forms
toolbar.
what i can't get is the check and uncheck portion to work.
I got as far as putting the check box.. assigning a macro and when i
checked it it would protect the sheet, but when i unchecked it it did
nothing.

Thanks again,
Lilbpaw
 
Tinkered around with code from a previous Tom Ogilvy post.
The sub below seems to work ok and do the stuff you want
when assigned to a checkbox from the forms toolbar ..

When the checkbox is ticked, it'll hide the formulas in D1:D3
and protect the sheet (no password) with A1:C3 unlocked
for data entry. When the tick is removed, it'll unprotect the sheet

Sub ProtectNHideFormulas()
sName = Application.Caller
If ActiveSheet.CheckBoxes(sName).Value = xlOn Then
Range("D1:D3").FormulaHidden = True
Range("A1:C3").Locked = False
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Else
ActiveSheet.Unprotect
End If
End Sub
 
in theory this may work.. I am going to test it out.

However my main question is.. in this sheet i made the group i need t
keep unlocked is in the center of other cells.

the locked ones are surrounding it.
So would i be able to add multiple lines of coding to lock al
necessary cells?

Thanks,
lilbpa
 
in theory this may work.. I am going to test it out.

However my main question is.. in this sheet i made the group i need t
keep unlocked is in the center of other cells.

the locked ones are surrounding it.
So would i be able to add multiple lines of coding to lock al
necessary cells?
Since they are not all in one row.

Thanks,
lilbpa
 
However my main question is.. in this sheet i made the group i need to
keep unlocked is in the center of other cells.
Click to expand...

Tried the slight variation below of the previous sub
and it seemed to work ok for me ..

Example situation:
In D1 is say: =SUM(A1:C1), D1 copied down to D11
The unlocked cells are in the "middle" of the range summed,
viz.: "B2:C2,B6:C6,B10:C10"

Sub ProtectNHideFormulas()
sName = Application.Caller
If ActiveSheet.CheckBoxes(sName).Value = xlOn Then
Range("B2:C2,B6:C6,B10:C10").Locked = False
Range("D1:D11").FormulaHidden = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Else
ActiveSheet.Unprotect
End If
End Sub
 
Back
Top