.Protect UserInterfaceOnly:=True deleting formulas?

[micfly]

A friend helped me with this code who is out of pocket now and maybe someone on here can help. The code was working fine until I noticed it was deleting formulas. The formulas being deleted are in protected cells out of the range the code is running in e.g. E3:E9. I'm assuming it has something to do with: Sheets("DataEntry").Protect UserInterfaceOnly:=True ??? I thought that statement would allow changes to the sheet without effecting the formulas? Here's the code:

Dim LR, i As Long, cellsToFill As Range
If Intersect(Target, Range("$Q$11:$Q$399")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
i = Target.Row
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets("DataEntry").Protect UserInterfaceOnly:=True
 
If Target.Value = "" And Target.Interior.ColorIndex = 36 Then
    MsgBox "This record was previously copied" & vbLf & _
        "to another worksheet." & vbLf & vbLf & _
        "If you are going to delete it, remember" & vbLf & _
        "to delete in the another worksheet too."
    Target.Interior.ColorIndex = 3
    GoTo getout
End If
 
Set cellsToFill = Union(Cells(i, 2), Cells(i, 3), Cells(i, 4))
 
If Target.Value = "" Then GoTo getout
If Application.CountA(cellsToFill) < 3 Then
    CreateObject("WScript.shell").popup _
        "please, fill all the required cells" & vbLf & vbLf & _
            "data will not be copied", 3, "hello"
    Target.Value = ""
GoTo getout
 
Else: Target.Interior.ColorIndex = 36
Target.Offset(, -15).Resize(, 14).Copy
 
Select Case UCase(Target.Value)
    Case [S4]
        With Sheets("MGR2")
            LR = .Cells(199, 2).End(xlUp).Row
            .Cells(LR + 1, 2).PasteSpecial
        End With
        MsgBox "the record was pasted into MGR2 sheet"
    Case [S5]
        With Sheets("MGR3")
            LR = .Cells(199, 2).End(xlUp).Row
            .Cells(LR + 1, 2).PasteSpecial
        End With
        MsgBox "the record was pasted into MGR3 sheet"
    Case [S3]
        With Sheets("MGR1")
            LR = .Cells(199, 2).End(xlUp).Row
            .Cells(LR + 1, 2).PasteSpecial
        End With
        MsgBox "the record was pasted into MGR1 sheet"
    Case [S6]
        With Sheets("MGR4")
            LR = .Cells(199, 2).End(xlUp).Row
            .Cells(LR + 1, 2).PasteSpecial
        End With
        MsgBox "the record was pasted into MGR4 sheet"
     Case Else
        Target.ClearContents
        Target.Interior.ColorIndex = xlNone
End Select
Target.Value = UCase(Target.Value)
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
getout:
Application.EnableEvents = True
 
End Sub

thx for any help

 

[Jerry Sullivan]

Hello micfly,

If you have set .Protect UserInterfaceOnly:=True, Excel allows macros to run as if the worksheet was unprotected.
It doesn't differentiate between cells with formulas and cells with values.

Here is a test that you can try to see the effect of UserInterfaceOnly being set True or False.

Sub ProtectFromAll()
'---Unprotect Sheet and put a formula in A1 before running
    With ActiveSheet
        .Range("A1").Locked = True
        .Protect UserInterfaceOnly:=False
        .Range("A1") = "New Value" 'causes error
    End With
End Sub

Sub ProtectFromUserOnly()
'---Unprotect Sheet and put a formula in A1 before running
    With ActiveSheet
        .Range("A1").Locked = True
        .Protect UserInterfaceOnly:=True
        .Range("A1") = "New Value" 'works- overwrites formula
    End With
End Sub

 

[micfly]

Hmm, okay, the test worked (on a new test sheet) but I still don't understand why it would delete formulas in my workbook?

 

[Jerry Sullivan]

Hmm, okay, the test worked (on a new test sheet) but I still don't understand why it would delete formulas in my workbook?

This line in your code Copies a 1 Row by 14 Column Range.....

Target.Offset(, -15).Resize(, 14).Copy

...then this part pastes that range at the specified location.

Select Case UCase(Target.Value)
    Case [S4]
        With Sheets("MGR2")
            LR = .Cells(199, 2).End(xlUp).Row
            .Cells(LR + 1, 2).PasteSpecial

So the code might be pasting over formulas, which would appear to "delete" them if the pasted cells were blank.

 

[micfly]

Thanks for helping. There are no formulas in the cells being pasted and there are no formulas in the cells pasted to. However, these values in the original cells being pasted are used for caculation values in other cells that contain formulas. Those formula cells are protected. For example, cell E3 contains a formula that derives a value from some of the data in B11:O11.
Once the macro runs the formula in E3 is gone.

 

[Jerry Sullivan]

Thanks for helping. There are no formulas in the cells being pasted and there are no formulas in the cells pasted to. However, these values in the original cells being pasted are used for caculation values in other cells that contain formulas. Those formula cells are protected. For example, cell E3 contains a formula that derives a value from some of the data in B11:O11.
Once the macro runs the formula in E3 is gone.

Remember, that as those test macros showed, the fact that the cells with the formulas are Locked and the sheet is Protected, will not prevent the macro from changing those cells if UserInterfaceOnly:=True.

Regarding how those cells are getting pasted to since that is not the intent of the macro, one possibility is that this line, isn't finding the last row with data that you want it to find.

LR = .Cells(199, 2).End(xlUp).Row

What this does is equivalent to the Selecting Cell B199 then pressing Ctrl-Up Arrow keys.

If you have data from B5:B100, the ActiveCell will move to B100 and in VBA LR will be assigned the value 100. This is what you are expecting your code to do - add to your list of items.

If instead you have data from B2:B200 an unexpected response happens.
Selecting Cell B199 then pressing Ctrl-Up Arrow keys will cause B2 to be Selected. That's because pressing Ctrl-Up Arrow keys doesn't find the last filled cell in the column, it finds the next cell going up that is filled at the point that cells change from blank to filled or filled to blank depending on the whether the current cell is blank or filled (that's a terrible explanation, but if you experiment with this it will be clearer).

It's hard to say if that's what happened in your case, but it's one possible explanation. Regardless of whether that was the cause, it would be better to revise your code to:

LR = .Cells(Rows.Count, 2).End(xlUp).Row

 

[micfly]

It is selecting the correct empty row to paste to. I tried changing the code per your advice but now I'm getting a run-time error:

With Sheets("MGR1")
            LR = .Cells(Rows.Count, 2).End(xlUp).Row
[COLOR=royalblue]here>[/COLOR]      [COLOR=sienna].Cells(LR + 1, 2).PasteSpecial[/COLOR]

 

[Jerry Sullivan]

It is selecting the correct empty row to paste to. I tried changing the code per your advice but now I'm getting a run-time error:

I believe the error is due to not having a PasteSpecial type like xlPasteValues or xlPasteAll. This wasn't in your orignal code which I assumed was not giving you a RunTime error; however for xl2007 at least this is a required parameter of PasteSpecial.

To just paste values...

With Sheets("MGR1")
            LR = .Cells(Rows.Count, 2).End(xlUp).Row
here>      .Cells(LR + 1, 2).PasteSpecial[COLOR="Blue"][B](xlPasteValues)[/B][/COLOR]

I don't know what else I can suggest to help you with your original question about why formulas were deleted. If you say the paste operation doesn't get near those cells, then I'm not seeing how that might have happened.

Is it a repeatable problem, meaning if you put the formulas back in and use the sheets, can you get the formulas to be deleted again?

 

[xenou]

My experience with UserInterfaceOnly:=True is that it is not reliable -- sometimes runtime errors occur even though code is supposed to be allowed to operate on the protected sheet. This could be happening to you. If the code is working on more than one sheet you have a lot of sheets to worry about protecting (headaches).

 

[micfly]

I believe the error is due to not having a PasteSpecial type like xlPasteValues or xlPasteAll.

here>      .Cells(LR + 1, 2).PasteSpecial[COLOR=blue][B](xlPasteValues)[/B][/COLOR]

Adding the xlPasteValues took care of the error. Sorry, I should have mentioned this is excel 07.

Is it a repeatable problem, meaning if you put the formulas back in and use the sheets, can you get the formulas to be deleted again?

Yes, it is repeatable. The first copy/paste is fine, but by the second copy/paste (when the formulas start having something to calculate) is when the formulas start disappearing. Two examples of formulas in E3:E4 =SUMPRODUCT(--(E11:E399="N"),--(F11:F399<>"L")) and =COUNTIF(F11:F399,"F")

My experience with UserInterfaceOnly:=True is that it is not reliable -- sometimes runtime errors occur even though code is supposed to be allowed to operate on the protected sheet. This could be happening to you. If the code is working on more than one sheet you have a lot of sheets to worry about protecting (headaches).

So what other option is there?