VBA to lock a sheet from changes

[muhleebbin]

I have the following code where if you change a percentage value in cell K1 it will calculate the upcoming years rates. Is there a way to add a toggle button that would disable the code from filling in the calculated fields as well as lock the values in I3:I54 so that no further changes can be made?

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.CutCopyMode = False

Dim strFormulas(1 To 1) As Variant
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

If Not Intersect(Target, Range("K1")) Is Nothing Then
    
    If ws.Range("K1") <> "" Then
    
    With ThisWorkbook.Sheets("Sheet1")
        .Range("I3:I54").ClearContents
    
        strFormulas(1) = "=IFERROR(MROUND(H3+(H3*$K$1),5),"""")"
        
        .Range("I3").Formula = strFormulas
        .Range("I3").Copy
        .Range("I3:I54").PasteSpecial Paste:=xlPasteFormulas
        .Range("I3").Select
    End With
    
    Else
        ws.Range("I3:I54").ClearContents
    End If

End If

Application.ScreenUpdating = True
Application.CutCopyMode = True

End Sub

 

[OaklandJim]

I usually put such "settings" in a worksheet called Control. I would also give a range name to the cell such as "DoUpdates". Wherever you put the toggle switch it would have a value of true or false. That is a good use of "Data Validation". Let's say that the toggle switch (cell) named DoUpdates is in Sheet 1.

Dim bDoUpdate As Boolean

bDoUpdate = Thisworkbook.Worksheets("Sheet1").Range("DoUpdates").Value  '<= you could use a cell address here if desired.

If not bDoUpdate then Exit Sub

If needed there are lots of websites that explain range names. Very powerful!

 

[muhleebbin]

I usually put such "settings" in a worksheet called Control. I would also give a range name to the cell such as "DoUpdates". Wherever you put the toggle switch it would have a value of true or false. That is a good use of "Data Validation". Let's say that the toggle switch (cell) named DoUpdates is in Sheet 1.

Dim bDoUpdate As Boolean

bDoUpdate = Thisworkbook.Worksheets("Sheet1").Range("DoUpdates").Value  '<= you could use a cell address here if desired.

If not bDoUpdate then Exit Sub

If needed there are lots of websites that explain range names. Very powerful!

Thanks for the answer OaklandJim!

I'm really sorry but kind of still figuring out where to put this code. Would it be within the code above or would I put it in a Module with it's own sub? I'd prefer to put this lock in on the 10 sheets for each location rather than a Control sheet (which ironically I already have ). That way I can lock the individual locations each rather than them all.

 

[muhleebbin]

Thanks for the answer OaklandJim!

I'm really sorry but kind of still figuring out where to put this code. Would it be within the code above or would I put it in a Module with it's own sub? I'd prefer to put this lock in on the 10 sheets for each location rather than a Control sheet (which ironically I already have ). That way I can lock the individual locations each rather than them all.

Just realized i'd put the code within the ToggleButton code.....ugghhhh

 

[muhleebbin]

Sub LocationButton()
 
If Sheets("Location Forecast").Shapes("Button 6").TextFrame.Characters.Text = "Locked" Then
    ActiveSheet.Unprotect
    Sheets("Location Forecast").Shapes("Button 6").TextFrame.Characters.Text = "Unlocked"
    Call UnlockLocation
Else
    ActiveSheet.Unprotect
    Sheets("Location Forecast").Shapes("Button 6").TextFrame.Characters.Text = "Locked"
    Call LockLocation
    ActiveSheet.Range("K1").Select
End If
 
End Sub

Sub LockLocation()

    Sheets("Location Forecast").Range("K1,I3:I55").Locked = True
    ActiveSheet.Protect
    ActiveSheet.EnableSelection = xlUnlockedCells

End Sub

Sub UnlockLocation()

    Sheets("Location Forecast").Range("K1,I3:I55").Locked = False
    ActiveSheet.Protect
    ActiveSheet.EnableSelection = xlUnlockedCells
   
End Sub