.Protect UserInterfaceOnly:=True deleting formulas?

M

micfly

Well-known Member
Joined
Sep 8, 2008
Messages
543
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:

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
 
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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?
Click to expand...
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.
Click to expand...
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.

ξ
 
Upvote 0
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.).
 
Upvote 0
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?
 
Upvote 0
Okay, back to the beginning:

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


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).
Code: 
     Case Else
        Target.ClearContents
        Target.Interior.ColorIndex = xlNone
 
Upvote 0
xenou said:
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).
Code: 
     Case Else
        Target.ClearContents
        Target.Interior.ColorIndex = xlNone
Click to expand...

Hi xenou,

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

Assuming this is Worksheet_Change event code, then this line...
Code: 
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. :biggrin:
 
Upvote 0
JS411 said:
Assuming this is Worksheet_Change event code, then this line...
Rich (BB code): 
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.
Click to expand...


Ahhh...I just noticed that should be
Rich (BB code): 
If Target.Cells.Count > 1 Then Exit Sub
 
Upvote 0
Ok, I've got to do some back-peddling. :oops:

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
I have a problem in audit trail with VBA
Replies
1
Views
135
Joe4
Joe4
A
  • Question
sometimes show "Method 'Range' of object '_Worksheet' failed" error and maybe not error!!
Replies
0
Views
405
abdelfattah
A
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
250
Gajendran Yadhav
G
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
230
ItalianPlatinum
I
L
  • Question
Help with VBA code
Replies
0
Views
329
LordSnow548
L

Forum statistics

Threads
1,214,540
Messages
6,120,106
Members
448,945
Latest member
Vmanchoppy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top