Using Checkbox to Change Cell Value and Lock Cell

Do you want to use checkbox on userform?

Try this attached file please...

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then Range("B1").Value = 100: Range("B1").Locked = False: Call change_normal: TextBox1.Value = "100"
If CheckBox1.Value = False Then Range("B1").Value = 9999: Range("B1").Locked = True: Call change_grey: TextBox1.Value = "9999"

End Sub

Sub change_grey()

Range("B1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With

End Sub

Sub change_normal()

Range("B1").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

I don't need this to be on a userform. Does it mean I can still use the same code and just make sure my checkbox names are the same?

But when I open the file and click on the checkbox, it gave me a runtime error 1004.

And for the value in B1, is there a way to make it dynamic? It will not always be "100". It will start off at 100 and user can change it. But I want the last value on B1 to be stored before checkbox is OFF and restored back to B1 after checkbox is ON again.

I made some minor changes to cemregunay's code. But I still can't figure out how to store the original value BEFORE it turns to "99999" (Checkbox OFF) and be able to bring it back when checkbox is ON. This value in Cell D10 can be edited whenever checkbox is ON. Any idea?

Formula:

Private Sub CheckBox1_Click()

Dim con_budg As Long

Dim con_inf As Long



con_inf = 99999



If CheckBox1.Value = True Then Range("D10").Value = con_budg: Range("D10").Locked = False: Call change_normal

If CheckBox1.Value = False Then Range("D10").Value = con_inf: Range("D10").Locked = True: Call change_grey



End Sub



Sub change_grey()



Range("D10").Select

    With Selection.Interior

        .Pattern = xlSolid

        .Color = RGB(217, 217, 217)

    End With



End Sub



Sub change_normal()



Range("D10").Select

    With Selection.Interior

        .Pattern = xlSolid

        .Color = RGB(255, 255, 0)

    End With



End Sub

Anyone has any idea?