.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

 

[micfly]

BTW, using the original code I first posted here. If I remove the '.Protect UserInterfaceOnly:=True' line.
Add 'ActiveSheet.Unprotect' here:

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
[COLOR=darkred]ActiveSheet.Unprotect[/COLOR]

And then add 'ActiveSheet.Protect' here:

'if all is OK then start copy & paste;
'first change collor of FIMGR cell to yellow, copy the record
Else: Target.Interior.ColorIndex = 36
[COLOR=darkred]ActiveSheet.Protect[/COLOR]
Target.Offset(, -15).Resize(, 14).Copy

all works perfectly.

 

[ZVI]

Get error on this line.

Have not idea why this line fails without testing workbook, it was in your code of post #22 for the Range("B11:B399,J11:J399") and comes from updated code of post #25

 

[ZVI]

all works perfectly.

Really good news, congrats!
Looks like it corresponds to Xenou’s warning in post #11.
The Protect/Unprotect is stable method when all code is not under control. And it’s a good reason to be happy in your case.
But if you are so curious to find the real reason of issue, then you may PM me to get my e-mail for analyzing the full code of your workbook, without sensitive data in it of course.

 

[xenou]

What I mean about the protection is that your code should not be attempting to delete E3 (period, end of story). So if the code deletes E3 when the worksheet is unprotected, and not otherwise, it means that somewhere your code *is* deleting E3.

I generally step through my code line by line and check every line as it executes to make sure I know what it is doing.

It's hard to say about these errors. They don't really make sense.

Target.Value = StrConv(Target.Text, vbProperCase)

Try instead:

Target.Value = StrConv(Target.[COLOR="Red"]Value[/COLOR], vbProperCase)

(personally I just don't give two figs about proper case - it doesn't work for McFly anyway, or McDonald or LeBlanc or MacDonough and so on).

You might need to say what value you are changing to proper case - not sure if the problem is that the value can't be converted or if it's something else. I don't know what happens with strconv, for instance, if the cell is empty (I don't use the strConv function very much).

Personally I would remove all protection until all the other bugs are worked out. Then work the protection back in.

 

[ZVI]

Let me share the analyzing of received workbook.

There are 162 weird cells having the formulas.
Their formulas sometimes disappear and replace by values at changing of other cells.
Sometimes formula can disappear even at reentering of it but one more reentering helps.

As the code of DataEntry sheet was posted here and previously analyzed it was clear that source of issue is not in the code.
Therefore I have commented the code of DataEntry and disable events typing Application.EnableEvents = False in Immediate window but it does not help.

Surprisingly issue remains even after all the code in workbook were commented.
But this gives me direct answer on a question what is going on – some controls of userforns are referred to the cells of DataEntry sheet with formulas .

The culprit Userform was identified by testing behavior after deleting userforms one by one.
There were 162 textboxes in culprit Userform with their property ControlSource referred to the cells with formulas.

Values of textboxes reflect the values of the referred cells received at userform’s design time.
Till the values of cells are the same as values of textboxes then nothing weird happens.
But if formula of the referred cell returns not the same value as in textbox then the problem comes - textbox receives new value and then puts it back to the referred cell, with formula erasing of course.

Note - the userform is not showed/activated at all.

How to replicate issue:
1. Put in cell A1 of Sheet1 the formula: =RAND()
2. Press Alt-F11 and add UserForm1
3. Put TextBox1 on the form
4. Set the ControlSource property of TextBox1: Sheet1!A1
5. On Sheet1 press F9 – formula A1 will return new value with disappearing of the formula

Therefore it is a way to replace cell’s formula by the value without any line of VBA code.

For example, the formula of A1 is =TODAY(), workbook is saved and sent to the customer.
On another day customer opens workbook and the value of A1 returns the current date with formula disappearing.
It's funny at least, sometimes it can be even helpful, is not it?

Regards

 

[p45cal]

Note - the userform is not showed/activated at all.

How to replicate issue:
1. Put in cell A1 of Sheet1 the formula: =RAND()
2. Press Alt-F11 and add UserForm1
3. Put TextBox1 on the form
4. Set the ControlSource property of TextBox1: Sheet1!A1
5. On Sheet1 press F9 – formula A1 will return new value with disappearing of the formula

How peculiar, and well done!
Now add a step:
1a. Protect the sheet
and prepare to have fun with the Esc key! (assuming Calculation is Automatic).

 

[micfly]

So Vladimir, are you saying it was my userforms?
I originally built the userforms using a control source reference on the textboxes but changed that to using the Userform's Initialize event to pull in the values from the worksheet and apply them to the correct TextBoxes. I left two of the old userforms (the control source ones) for reference only and they were never called by a command button. Those were frmDataEntryRef and frmMGR1Ref. Sorry, just trying to understand.

Also, since it's working now, is it okay the way it is?

 

[ZVI]

So Vladimir, are you saying it was my userforms?
I originally built the userforms using a control source reference on the textboxes but changed that to using the Userform's Initialize event to pull in the values from the worksheet and apply them to the correct TextBoxes. I left two of the old userforms (the control source ones) for reference only and they were never called by a command button. Those were frmDataEntryRef and frmMGR1Ref. Sorry, just trying to understand.

Also, since it's working now, is it okay the way it is?

Yes, it was frmDataEntryRef which is not called via code.
I've sent you 12 Dec the fixed file Tracking_01.xlsm

 

[ZVI]

Micfly has confirmed that it solves the problem, and UserInterfaceOnly is working now.

Now add a step:
1a. Protect the sheet
and prepare to have fun with the Esc key! (assuming Calculation is Automatic).

Nice one!
Warning: Infinite random numbers! Esc can't escape! For colleagues only!

 

[micfly]

Geez, such a simple fix and such a nightmare. This was driving me crazy.
Thanks for all of your help!!! But one more (kind of loaded) question. When is it okay to use a controlsource to reference a cell on a userform?