none
How to protect Excel cells while allowing VBA to modify them? RRS feed

  • Question

  • I want to allow users to modify inputs in some Unlocked cells and have the other cells update accordingly. I also want to prevent users from clobbering those other cells, so those are Locked.

    Protect Sheet allows ordinary formulas in Locked cells to execute but, by default, prevents VBA functions from executing when their input cells are modified. So, after consulting the Excel 2013 developer reference's descriptions of the Worksheet.Protect method and Worksheet object, I added these statements to my initialization routine:

    Worksheets(1).Activate
    ActiveSheet.Protect "", UserInterfaceOnly = True

    The 2nd statement enables Protect Sheet with a null password successfully, but the UserInterfaceOnly setting isn't having the effect I expected, i.e., allowing the VBA functions to execute when their input cells are changed. (I've noticed it seems to enable the Allow Users to Edit Objects protection option.)

    I also tried := True, but that doesn't help.




    • Edited by therealdp Thursday, December 25, 2014 5:36 PM
    Thursday, December 25, 2014 5:19 PM

Answers

  • It should actually be

    ActiveSheet.Protect UserInterfaceOnly:=True

    but if that doesn't help, you can unprotect the sheet at the beginning of your macros and protect it again at the end:

    Sub MyMacro()
        ' Unprotect the sheet
        ActiveSheet.Unprotect
        ' Code to manipulate cells goes here
        ...
        ' Protect the sheet
        ActiveSheet.Protect
    End Sub


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

    • Marked as answer by therealdp Tuesday, December 30, 2014 12:34 AM
    Thursday, December 25, 2014 9:26 PM

All replies

  • It should actually be

    ActiveSheet.Protect UserInterfaceOnly:=True

    but if that doesn't help, you can unprotect the sheet at the beginning of your macros and protect it again at the end:

    Sub MyMacro()
        ' Unprotect the sheet
        ActiveSheet.Unprotect
        ' Code to manipulate cells goes here
        ...
        ' Protect the sheet
        ActiveSheet.Protect
    End Sub


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

    • Marked as answer by therealdp Tuesday, December 30, 2014 12:34 AM
    Thursday, December 25, 2014 9:26 PM
  • Thanks for your reply, especially given the date!

    I tried

    ActiveSheet.Protect UserInterfaceOnly:=True

    and

    ActiveSheet.Protect UserInterfaceOnly = True

    but neither had any effect. (BTW, I've seen code that uses := and = but have been unable to learn the difference. Also, thanks for correcting my Password syntax -- my PS may provide a related chuckle.)

    I tried your alternative but encountered a new problem. The following code, which is in ThisWorkbook, executes when the Workbook opens:

    Private Sub Workbook_Open()
            Worksheets(1).Activate
            ActiveSheet.Protect
            ...
    End Sub

    If the Worksheet was Unprotected when the file was last saved, it becomes Protected when the file is opened and macros are enabled. However, the protection toggling in the following, which is in a module, fails:

    Function Show_RGB(Y, u, v, Swatch) As Variant
       Worksheets(1).Unprotect
       MsgBox "unprotected"
       ...
       Worksheets(1).Protect
       MsgBox "protected again and exiting"
    End Function

    The lock icon on the worksheet's tab remains visible at the first breakpoint, and the expected runtime error from attempting to execute the subsequent code on the still-protected worksheet occurs. I've also tried ActiveSheet.Unprotect -- it makes no difference.

    PS - Beware of commenting out the UserInterfaceOnly part of an ActiveSheet.Protect statement. Somehow, I found myself facing a password-protected worksheet that I couldn't determine the password for! I was rescued by a worksheet password breaker at http://uknowit.uwgb.edu/page.php?id=28850 . "Thank you!" to the coder, Marlee P.



    • Edited by therealdp Friday, December 26, 2014 6:14 AM fixed URL
    Friday, December 26, 2014 6:10 AM
  • I'm afraid I cannot explain the problems you're experiencing.

    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


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

    Friday, December 26, 2014 10:09 AM
  • Friday, December 26, 2014 4:05 PM
  • The function fails because you cannot change characteristics of a chart in a worksheet function. A worksheet function can only return a value to the cell that contains the formula.

    You can use the Worksheet_Change event to modify the chart instead. The function can be simplified to

    Function Show_RGB(Y, u, v, Swatch) As Variant
        Dim R As Single, G As Single, B As Single
        On Error GoTo ErrHandler
        R = R_from_Yuv(Y, u, v)
        G = G_from_Yuv(Y, u, v)
        B = B_from_Yuv(Y, u, v)
        Show_RGB = Array(R, G, B)   ' Allow <0 and >255 in spreadsheet
    ExitHandler:
        Exit Function
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Function

    The Worksheet_Change event procedure in the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim R As Single, G As Single, B As Single, Swatch As Integer
        Dim rng As Range
        On Error GoTo ErrHandler
        If Not Intersect(Range("B3:D14"), Target) Is Nothing Then
            Worksheets(1).Unprotect
            For Each rng In Intersect(Range("B3:D14"), Target)
                R = Cells(rng.Row, 5)
                G = Cells(rng.Row, 6)
                B = Cells(rng.Row, 7)
                Swatch = rng.Row - 2
                R = WorksheetFunction.Max(0, (WorksheetFunction.Min(255, R)))   ' ...avoid runtime error when changing displayed color
                G = WorksheetFunction.Max(0, (WorksheetFunction.Min(255, G)))
                B = WorksheetFunction.Max(0, (WorksheetFunction.Min(255, B)))
                With Worksheets(1).ChartObjects(6).Chart.SeriesCollection(1)
                   .Points(Swatch).MarkerBackgroundColor = RGB(R, G, B)
                   .DataLabels(Swatch).Font.Color = RGB(R, G, B)
                End With
            Next rng
        End If
    ExitHandler:
        Worksheets(1).Protect
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    See https://www.dropbox.com/s/nylonrp0y86lq1c/FAA%20palette%20designer%20Beta%2002.xlsm?dl=1


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

    Saturday, December 27, 2014 12:35 AM
  • The function fails because you cannot change characteristics of a chart in a worksheet function.

    But the function works fine for me if the worksheet is unprotected. Please try it and see. Does it error for you in that case?

    EDIT: I think the problem may be that a worksheet function can't unprotect a chart. Maybe it can't unprotect anything -- I'm not sure yet. So I think you're onto something, but your diagnosis isn't quite right.

    So far, I haven't found a chart-unprotect syntax that works within that function. For example:

    MsgBox "unprotecting"
     Worksheets(1).ChartObjects(6).Unprotect
     MsgBox "unprotected"

    The 1st MsgBox fires, but the 2nd doesn't and I get a runtime error: "Application defined or object-defined error." (Thanks for showing me error handling.)



    • Edited by therealdp Saturday, December 27, 2014 8:09 PM
    Saturday, December 27, 2014 1:12 PM
  • You're correct. You can use the function without unprotecting/protecting the sheet:

    Function Show_RGB(Y, u, v, Swatch) As Variant
        R = R_from_Yuv(Y, u, v)
        G = G_from_Yuv(Y, u, v)
        B = B_from_Yuv(Y, u, v)
        Show_RGB = Array(R, G, B)   ' Allow <0 and >255 in spreadsheet, but...
        R = WorksheetFunction.Max(0, (WorksheetFunction.Min(255, R)))   ' ...avoid runtime error when changing displayed color
        G = WorksheetFunction.Max(0, (WorksheetFunction.Min(255, G)))
        B = WorksheetFunction.Max(0, (WorksheetFunction.Min(255, B)))
        With Worksheets(1).ChartObjects(6).Chart.SeriesCollection(1)
           .Points(Swatch).MarkerBackgroundColor = RGB(R, G, B)
           .DataLabels(Swatch).Font.Color = RGB(R, G, B)
        End With
    End Function

    if you protect the sheet with UserInterfaceOnly:=True and DrawingObjects:=False when the workbook is opened:

    Private Sub Workbook_Open()   'Set initial stepsize = 0.001 and establish control-key assignments
        Worksheets(1).Activate
        ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=False
        Range("B23").Value = 0.001
        Application.OnKey "^{.}", "IncStepsize"
        Application.OnKey "^{,}", "DecStepsize"
        Application.OnKey "^{LEFT}", "LeftArrow"
        Application.OnKey "^{RIGHT}", "RightArrow"
        Application.OnKey "^{DOWN}", "DownArrow"
        Application.OnKey "^{UP}", "UpArrow"
    End Sub


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

    Sunday, December 28, 2014 12:49 AM

  • Private Sub Workbook_Open()   'Set initial stepsize = 0.001 and establish control-key assignments
        Worksheets(1).Activate
        ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=False
    ...
    End Sub
    Did you confirm that Protect statement works for you? When I try it, the subsequent attempt to alter the chart produces: "Method 'MarkerBackgroundColor' of object 'Point' failed" for me.
    Monday, December 29, 2014 2:41 PM
  • Yes, I did. The version on my DropBox works without error for me. I can edit the numbers and thereby modify the chart. The link is the same:

    https://www.dropbox.com/s/nylonrp0y86lq1c/FAA%20palette%20designer%20Beta%2002.xlsm?dl=1


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

    Monday, December 29, 2014 4:25 PM
  • The version I downloaded from you produces the same error for me, even after rebooting.

    I'm using Office 2011 for OSX and expect you're using 2013 or 2007 for Windows. I suspect chart-specific protection functions aren't supported in 2011, although I can't locate any 2011 documentation or online discussions that confirm that. (In fact, I can't locate any 2011 VBA documentation at all!)

    Fortunately, the ActiveSheet.Protect/Unprotect approach that you suggested initially works for me now. I've no idea why it failed when I first tried it. It's inelegant, but gets the job done.

    Thanks very much for your help and the coding examples, which taught me several other things. I'll happily buy you a nice piece of Beemster if we ever meet!  :)

    Cordially,

    Dave

    Tuesday, December 30, 2014 12:32 AM
  • It would have helped if you had mentioned outright that you're using Excel for the Mac instead of Windows. I have no experience with recent Mac versions.

    But thanks for the offer! :)


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

    Tuesday, December 30, 2014 10:31 AM
  • It was a deliberate choice. Most people's experience here is with Win versions, and if they know the problem involves a Mac, they're too quick to assume the problem lies there and give up, or not try to help at all. Please forgive my ruse.

    Dave

    Tuesday, December 30, 2014 2:23 PM