.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

 

[xenou]

As far as relying (or not relying) on UserInterfaceOnly option it's a matter of choice - I prefer not to use a lot of protection in vba-enabled applications as it adds a lot of overhead - you have to manage all of this protecting/unprotecting and it increases the risk of crashes if you get it wrong. I just keep backups and if a user screws up by deleting something they shouldn't its a learning opportunity - next time they will know better.

As far as having formulas deleted, you have to find out where the code that deletes things is happening and change that code. I'm not sure because when you say it deletes formulas you haven't said on what sheet, and there are several sheets referenced in your code.

ξ

Note: RE the UI setting -- I consider UserInterfaceOnly unreliable because I have a worksheet that I use where I employ this - the sheet activate event protects the worksheet with the UIOnly setting. This is the only way the worksheet is protected. But it still crashes on occasion when I try to edit the sheet in code (which is also the only way I edit the sheet). So I have left it as it is but about once every couple months it crashes, I have to unprotect the sheet manually, and run the edits again. I don't re-protect it manually. Rather I let the sheet activate event handle that (UIOnly=True). But still it will crash again in the same way later on (unpredictably). Seems to me there must be some bug in this feature but I don't know for sure.

 

[micfly]

gEEZ, this is getting complicated The only way I can use this workbook is to keep it protected. I have tons of formulas and they are all interconnected somehow. Without the formula cells protected it would blow all my formulas out?

This was just an added function I was trying for. None of the other code uses UserInterfaceOnly. That's what's confusing to me as to why I need it with this code or if there was another option to make it work.

 

[xenou]

gEEZ, this is getting complicated The only way I can use this workbook is to keep it protected. I have tons of formulas and they are all interconnected somehow. Without the formula cells protected it would blow all my formulas out?

Why would your formulas blow out if the cells aren't protected?

This was just an added function I was trying for. None of the other code uses UserInterfaceOnly. That's what's confusing to me as to why I need it with this code or if there was another option to make it work.

Protecting or unprotecting of itself doesn't delete formulas. The real problem is that your code is not correct for what it is supposed to do - it's not as if it's okay to delete the formulas when they are protected and not okay when they are not protected. You don't want them deleted, period. Though for what it's worth relying on UserInterfaceOnly = True is potentially unreliable.


I asked you what sheet the problem is ocurring on - what formulas are being deleted on what sheet? I can't tell from reading your code.

ξ

 

[micfly]

I have no idea why the formulas get deleted when they are not protected. I thought that was protection was for. They work perfectly when protected. Only when this code ran did I start having problems.

All the data is entered in the main sheet (DataEntry). This code was pasting the data (from DataEntry) to the other sheets (MGR1, MGR2, etc.).

 

[xenou]

Okay, but let me ask one more time, on which sheet are the formulas being deleted? On DataEntry? MGR1? MGR2? All of the MGR sheets?

 

[micfly]

Sorry, you're very patient The formulas are being deleted on the DataEntry sheet.

 

[xenou]

Okay, back to the beginning:

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

1) Your code has an End Sub but not a Start. I am assuming that this is a change event. Can you confirm that? If you are not sure, post the complete code with the beginning of the sub.

2) Your code reacts to you typing in a value:
Select Case UCase(Target.Value)
What are you typing in when you get the error, and what cell are you typing into?
If you want to know where I am going, this looks suspicious (it will delete the cell you've typed data into. It is probably the problem).

     Case Else
        Target.ClearContents
        Target.Interior.ColorIndex = xlNone

 

[Jerry Sullivan]

2) Your code reacts to you typing in a value:
Select Case UCase(Target.Value)
What are you typing in when you get the error, and what cell are you typing into?
If you want to know where I am going, this looks suspicious (it will delete the cell you've typed data into. It is probably the problem).

     Case Else
        Target.ClearContents
        Target.Interior.ColorIndex = xlNone

Hi xenou,

Hope it isn't bad Netiquette to jump back into this.

Assuming this is Worksheet_Change event code, then this line...

If Target.Count > 1 Then Exit Sub

...should limit the formula-overwriting damage of Target.ClearContents to 1 Cell. That would mean micfly would need to be typing values directly over formulas without realizing it, which is unlikely.

Earlier in this thread I expressed that the most likely steps that are doing the overwriting are the PasteSpecial Method operations..
...I'm sticking with that story for now.

 

[Jerry Sullivan]

Assuming this is Worksheet_Change event code, then this line...

If Target.Count > 1 Then Exit Sub

...should limit the formula-overwriting damage of Target.ClearContents to 1 Cell. That would mean micfly would need to be typing values directly over formulas without realizing it, which is unlikely.

Ahhh...I just noticed that should be

If Target.Cells.Count > 1 Then Exit Sub

 

[Jerry Sullivan]

Ok, I've got to do some back-peddling.

I use:
If Target.Cells.Count > 1 Then Exit Sub
but I see that
If Target.Count > 1 Then Exit Sub
works the same.

And micfly has clarified that the formulas are being deleted from Sheet DataEntry. So my theory that the .PasteSpecial Method is where the cells were being overwritten is busted because those .PasteSpecial operations are only performed on the MGR# sheets.

micfly, Will you please post the top part of your procedure as xenou requested? If this is Worksheet_SelectionChange code, then clicking on the cells with formulas might delete them.

Also, do you have any other Worksheet event code or Workbook event code in this Workbook?