Linking Cells
 

I have a macro written where once you check a box it paste's a set
word into cells on other sheets. I have those other sheets locked so
that no one can edit them. The macro works when the cells are not
protected but does not work when the cells are protected. Does anyone
know a way around this? This is the code i have for the checkboxes.

'Checkbox code
With CheckBox1
If .Value = True Then
Worksheets("RENOVATION").Range("B6:E6").Value = "Leased"
Worksheets("BASE BUILDING").Range("B6:E6").Value =
"Leased"
Else
Worksheets("RENOVATION").Range("B6:E6").ClearConte nts
Worksheets("BASE BUILDING").Range("B6:E6").ClearContents
End If
End With

Linking Cells
 

Try this:
'Checkbox code
ActiveSheet.Unprotect
With CheckBox1
If .Value = True Then
Worksheets("RENOVATION").Range("B6:E6").Value = "Leased"
Worksheets("BASE BUILDING").Range("B6:E6").Value =
"Leased"
Else
Worksheets("RENOVATION").Range("B6:E6").ClearConte nts
Worksheets("BASE BUILDING").Range("B6:E6").ClearContents
End If
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




" wrote:

I have a macro written where once you check a box it paste's a set
word into cells on other sheets. I have those other sheets locked so
that no one can edit them. The macro works when the cells are not
protected but does not work when the cells are protected. Does anyone
know a way around this? This is the code i have for the checkboxes.

'Checkbox code
With CheckBox1
If .Value = True Then
Worksheets("RENOVATION").Range("B6:E6").Value = "Leased"
Worksheets("BASE BUILDING").Range("B6:E6").Value =
"Leased"
Else
Worksheets("RENOVATION").Range("B6:E6").ClearConte nts
Worksheets("BASE BUILDING").Range("B6:E6").ClearContents
End If
End With

Linking Cells
 

I'd try something like this

Sub test2()
'Checkbox code

Call CheckBoxCheck("Renovation", CheckBox1)
Call CheckBoxCheck("Base Building", CheckBox1)

End Sub
Sub CheckBoxCheck(myWSName As String, myCheckBox As Variant)
Dim Protection As Boolean

Dim myWS As Worksheet
Set myWS = Nothing
On Error Resume Next
Set myWS = Worksheets(myWSName)
On Error GoTo 0
If myWS Is Nothing Then
MsgBox ("Worksheet " & myWSName & " does not exist in the active
workbook")
Exit Sub
End If

If myWS.ProtectionMode = True Then
Protection = True
myWS.Unprotect
If myCheckBox.Value Then
myWS.Range("B6:E6").Value = "Leased"
Else
myWS.Range("B6:E6").ClearContents
End If
End If
If Protection Then
myWS.Protect
End If

End Sub

--
HTH,
Barb Reinhardt



" wrote:

I have a macro written where once you check a box it paste's a set
word into cells on other sheets. I have those other sheets locked so
that no one can edit them. The macro works when the cells are not
protected but does not work when the cells are protected. Does anyone
know a way around this? This is the code i have for the checkboxes.

'Checkbox code
With CheckBox1
If .Value = True Then
Worksheets("RENOVATION").Range("B6:E6").Value = "Leased"
Worksheets("BASE BUILDING").Range("B6:E6").Value =
"Leased"
Else
Worksheets("RENOVATION").Range("B6:E6").ClearConte nts
Worksheets("BASE BUILDING").Range("B6:E6").ClearContents
End If
End With