Unlock Form Controls

[briandpearson]

I have a locked sheet where the value of A1 is tied to a form control. It seems the only way for A1 to update is for A1 to be unlocked. Is there a way to keep A1 locked and still allow it to update depending on whether the control is checked or not?

Any help would be greatly appreciated. Thanks!

 

[BrandonWLH]

Yes, you need to lock it with a flag.

ThisWorkbook.Worksheets("mySheet").Protect "mypassword", UserInterfaceOnly:=True

This permits vba to still do its thing but keeps the user from manually entering things.

 

[Jaafar Tribak]

Follow these steps :

1- Remove the existing cell link to the form control .

2- Place the following code in a new standard module :

Option Explicit


Private Sub SetOnActionProp()

    'change these consts as required.
    Const THE_SHEET = "Sheet4"
    Const CONTROL_NAME = "Check Box 1"
    Const LINKED_CELL = "A1"
    
    Sheets(THE_SHEET).Shapes(CONTROL_NAME).OnAction = "'Macro " & _
    Chr(34) & THE_SHEET & Chr(34) & "," & Chr(34) & _
    CONTROL_NAME & Chr(34) & "," & Chr(34) & LINKED_CELL & Chr(34) & "'"

End Sub

Sub Macro(ByVal THE_SHEET As String, _
 ByVal CONTROL_NAME As String, _
 ByVal LINKED_CELL As String)

    With Sheets(THE_SHEET)
    .Unprotect
    .Shapes(CONTROL_NAME).ControlFormat.LINKEDCELL = LINKED_CELL
    .Protect
    .Shapes(CONTROL_NAME).ControlFormat.LINKEDCELL = ""
    End With

End Sub

3- Now run the SetOnActionProp once and that's it.

Note : The above works for a checkbox change this and the const values at the start of the SetOnActionProp routine to suit.