excel - VBA to greyout the sheet

Question

Continuing from my previous question!VBA to change the name of the Activex button

I would like to grey out the sheet until user clicks on enable button.So that it would looks user friendly and he understand that he needs to click on button inroder to edit.

Please suggest

Answer 1

You can make two buttons and link them to something like the code below. Determine the formatting of the locked cells (i find that grey fill with white font is sugestive). Ex.1 Ex.2

Sub Unprotect()
    With Sheets("Sheet2")
        .Shapes("Edit").Visible = False
        .Shapes("Rounded Rectangle 2").Visible = True
        .Unprotect
        .Cells.ClearFormats
    End With
End Sub

Sub Protect()
    Sheets("Sheet2").Select
    ActiveSheet.Shapes("Rounded Rectangle 2").Visible = False
    ActiveSheet.Shapes("Edit").Visible = True
    With Sheets("Sheet2").Cells
        .Interior.Pattern = xlSolid
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorDark1
        .Interior.TintAndShade = -0.149998474074526
        .Interior.PatternTintAndShade = 0
        .Font.ThemeColor = xlThemeColorDark1
        .Font.TintAndShade = 0
    End With
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub