none
Errors With Sheet Protected. RRS feed

  • Question

  • Thanks in advance for your help. I work in Access but took this on for a friend and Excel is so different.

    This code works perfectly without the sheet protected. With the sheet protected I get this error:

    "Unable to set the NumberFormat property of the Range class"  All of the cells listed in the code are unprotected.  Here is the code being used:

    Private Sub Phase1A1_Btn_Click()
    On Error GoTo Phase1A1_Err

    ' Phase 1, Tech A1

        If Range("D12").Value = "" Then                                                 ' If this control is empty.
            Range("D12").Value = Round(Now * 1440, 0) / 1440                ' Time stamp current time & round to minute.
        Else
            If Range("D14").Value = "" Then                                             ' If this control is empty.
                Range("D14").Value = Round(Now * 1440, 0) / 1440            ' Time stamp current time & round to minute.
                Range("H12").Value = (Range("D14") - Range("D12"))          ' Put the time difference in H12.
                Range("H31").Formula = "=Sum(H12,H17,H22,H27)"              ' Sum of H12,H17,H22,H27 in H31.
            End If
           
        Call AdjValPhase1A                                                                       ' Call the function to adjust the values.
       
        End If
       
    Phase1A1_Exit:
        Exit Sub
    Phase1A1_Err:
        MsgBox Error$
        Resume Phase1A1_Exit
    End Sub

    Public Function AdjValPhase1A()
    On Error GoTo AdjValPhase1A_Err

    ' Phase 1, Tech A

        Range("G33").Value = Range("H31") * 60 * 24                      ' Convert time in H31 to minutes and put in G33.
        Range("AI5") = "=Sum(G33,R33,AC33,AN33)"                       ' Sum G33,R33,AC33,AN33 in AI5.

        Range("H12,H17,H22,H27").NumberFormat = "[h]:mm"         ' Format to hours and minutes, hours [ ] can go above 24.
        Range("H31").NumberFormat = "[h]:mm"                             ' Format to hours and minutes, hours [ ] can go above 24.

        If Range("AI6").Value > Range("R6").Value Then                   ' If the actual is greater than the target.
            Range("AI6").Interior.Color = RGB(255, 0, 0)                    ' Set the fill color to red.
            Range("AI6").Font.Color = RGB(255, 255, 0)                     ' Set the font color to yellow.
        Else                                                                                    ' If the actual is less than the target.
            Range("AI6").Interior.Color = RGB(255, 255, 0)                 ' Set the fill color to yellow.
            Range("AI6").Font.Color = RGB(0, 0, 0)                             ' Set the font color to black.
        End If
       
    AdjValPhase1A_Exit:
        Exit Function
    AdjValPhase1A_Err:
        MsgBox Error$
        Resume AdjValPhase1A_Exit
    End Function

    Thank you for the help - Jerry215

    Wednesday, January 10, 2018 12:44 PM

Answers

  • You can unprotect/reprotect the sheet in the code:

    At the beginning of AdjValPhase1A, insert the line

        ActiveSheet.Unprotect

    If a password has been set, provide it in the code:

        ActiveSheet.Unprotect Password:="topsecret"

    Below AdjValPhase1A_Exit, insert the line

        ActiveSheet.Protect

    or

        ActiveSheet.Protect Password:="topsecret"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jerry215 Wednesday, January 10, 2018 2:48 PM
    Wednesday, January 10, 2018 1:07 PM

All replies

  • You can unprotect/reprotect the sheet in the code:

    At the beginning of AdjValPhase1A, insert the line

        ActiveSheet.Unprotect

    If a password has been set, provide it in the code:

        ActiveSheet.Unprotect Password:="topsecret"

    Below AdjValPhase1A_Exit, insert the line

        ActiveSheet.Protect

    or

        ActiveSheet.Protect Password:="topsecret"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jerry215 Wednesday, January 10, 2018 2:48 PM
    Wednesday, January 10, 2018 1:07 PM
  • Alternatively, allow formatting cells when you protect the sheet:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 10, 2018 1:08 PM
  • That worked great!!  Thank you!
    Wednesday, January 10, 2018 2:46 PM