Click event for checkbox from Forms toolbar
Remember that locked cells don't mean much unless the worksheet is protected.
And to change the lockedness of a cell, you'll have to unprotect the worksheet.
Option Explicit
Sub ChkBoxMSFree()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
If myCBX.Value = xlOn Then
'do what you want if it's checked.
'go to cell(s) and set values.
activesheet.unprotect password:="hi"
Range("MSRent").Locked = False
Range("MSRent").Value = ""
Range("MSRent").Locked = True
activesheet.protect password:="hi"
Else
'do what you want if it's not checked.
'go to cell(s) and set values.
activesheet.unprotect password:="hi"
Range("MSRent").Locked = False
activesheet.protect password:="hi"
End If
End Sub
Carolyn wrote:
Thank you very much again, Dave.
One more twist. I would also like to be able to disable specific worksheet
cells. First, I clear the contents of the cell, then I want to disable it.
(Note the workbook/sheet will ultimately be protected.) Basically, when one
checkbox is clicked, I want to turn off a cell entry. When the checkbox is
clicked again, I want to turn on cell entry.
Using the following line of code is causing an error.
Range("MSRent").Locked = True
Trying to select the cell first isn't helping.
Range("MSRent").Select
Selection.Locked = True
Here's the code, with the current incorrect lines for locking/disabling the
cell.
Option Explicit
Sub ChkBoxMSFree()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
If myCBX.Value = xlOn Then
'do what you want if it's checked.
'go to cell(s) and set values.
Range("MSRent").Locked = False
Range("MSRent").Value = ""
Range("MSRent").Locked = True
Else
'do what you want if it's not checked.
'go to cell(s) and set values.
Range("MSRent").Locked = False
End If
End Sub
--
Dave Peterson
|