Locking cells with a checkbox using a macro.
 

Hello. I have an ongoing problem. I am trying to lock cells so they
cannot be changed as long as a checkbox on the active sheet is checked.
I have some code, but it runs an error and doesn't quite work anyway.
I have another macro that fills the boxes with values, but I want the
users to be able to adjust those values. Only after the checkbox is
checked, I want the access to those cells to be locked. Here is a
sampling of the code I have so far:

Sub chkLockAttribs_Click()
'
'Private Sub chkLockAttribs_Click()


Dim myRng As Range
Dim myPwd As String


myPwd = "secret"


Set myRng = ActiveSheet.Range("C18, D19:D23, D25, D26:F26, J19:J23,
P33, P35")


ActiveSheet.Unprotect Password:=myPwd
myRng.Locked =
ActiveSheet.OLEObjects("chkLockAttribs").Object.Va lue
ActiveSheet.cmdRoll.Visible = Not
(ActiveSheet.chkLockAttribs.Value)
ActiveSheet.Protect Password:=myPwd



End Sub

The checkbox is chkLockAttribs and the button linked to a macro
generating values is cmdRoll. C18, D19:D23, D25, D26:F26, J19:J23,
P33, P35 is the range of cells I want to lock after the checkbox is
checked. I don't really need a password protection on this. It's more
a convienence thing.

So any suggestions? Any and all help appreciated.

-SplatterKat-

aka -Robert Lizak-

Locking cells with a checkbox using a macro.
 

I put a checkbox from the control toolbox toolbar on the worksheet. I named it
"chklockattribs".

I put a commandbutton from the control toolbox toolbar on that same worksheet.
I named it "cmdRoll".

I doubleclicked on that checkbox and pasted this into the code window that
opened:

Option Explicit
Private Sub chkLockAttribs_Click()

Dim myRng As Range
Dim myPwd As String

myPwd = "secret"

Set myRng = Me.Range("C18, D19:D23, D25, D26:F26, J19:J23,P33, P35")
Me.Unprotect Password:=myPwd
myRng.Locked = Me.chkLockAttribs.Value
Me.cmdRoll.Visible = Not (Me.chkLockAttribs.Value)
Me.Protect Password:=myPwd

End Sub


It worked fine for me.

If you used shapes from the Forms toolbar, make sure you include that info in
any followup.

SplatterKat wrote:

Hello. I have an ongoing problem. I am trying to lock cells so they
cannot be changed as long as a checkbox on the active sheet is checked.
I have some code, but it runs an error and doesn't quite work anyway.
I have another macro that fills the boxes with values, but I want the
users to be able to adjust those values. Only after the checkbox is
checked, I want the access to those cells to be locked. Here is a
sampling of the code I have so far:

Sub chkLockAttribs_Click()
'
'Private Sub chkLockAttribs_Click()

Dim myRng As Range
Dim myPwd As String

myPwd = "secret"

Set myRng = ActiveSheet.Range("C18, D19:D23, D25, D26:F26, J19:J23,
P33, P35")

ActiveSheet.Unprotect Password:=myPwd
myRng.Locked =
ActiveSheet.OLEObjects("chkLockAttribs").Object.Va lue
ActiveSheet.cmdRoll.Visible = Not
(ActiveSheet.chkLockAttribs.Value)
ActiveSheet.Protect Password:=myPwd

End Sub

The checkbox is chkLockAttribs and the button linked to a macro
generating values is cmdRoll. C18, D19:D23, D25, D26:F26, J19:J23,
P33, P35 is the range of cells I want to lock after the checkbox is
checked. I don't really need a password protection on this. It's more
a convienence thing.

So any suggestions? Any and all help appreciated.

-SplatterKat-

aka -Robert Lizak-

--

Dave Peterson