ws.Protect UserInterFaceOnly feature not working when inserting columns

    Hello all,


    I'm protecting my sheet using


    Code
    ws.Protect Password:="password", UserInterFaceOnly:=True


    Theoretically, this should allow me to make any changes to the sheet through VBA without the need to unprotect the sheet. However, the code bugs out on the following line:


    Code
    With ws
    .Columns("Z:Z").Insert Shift:=xlToRight
End With


    I already know that you need to run .Protect with UserInterFaceOnly every time the workbook is open, it's adding protection using my Workbook_Open event. But, it seems like there are exceptions to what can be manipulated on a sheet with VBA even if UserInterFaceOnly is set to True, in my case adding new columns. Is anyone able to confirm/deny this? I would prefer not to unprotect the sheet every time I run the macro, but I was curious if that's even an option.

    Re: ws.Protect UserInterFaceOnly feature not working when inserting columns


    Works for me on Win7, Excel 2010.


    Of course if you violate a rule like data in that last column and the shift would drop data off, Excel would tell you that it won't do it.


    You might make sure that the ws protected the same ws you use for column insertion.


    Here is what I use in ThisWorkbook's Open event.

    Code
    Private Sub Workbook_Open()
  Dim ws As Worksheet
  For Each ws In Worksheets
    ws.Protect "ken", UserInterfaceOnly:=True 'True allows code to change data.
  Next ws
End Sub

    Re: ws.Protect UserInterFaceOnly feature not working when inserting columns


    Kenneth, thanks for testing the code! I've deleted all columns to the right of the last column and saved the file (I had no data in the last column, but apparently Excel thought otherwise). This fixed the issue, must have been a glitch with the formatting.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login