Data Validation User Error Causes Workbook to Unprotect Itself

[Demo8II]

This one has got me stumped. Any help would be severely appreciated.

I have built an occupancy projection spreadsheet for our employees in the field. They are to project move ins and move outs once a week. After the appropriate week is passed, those projection cells are programmed to lock via a worksheet level event macro.

Also, I have data validation in the projection cells, so that the user has to enter move outs as a negative number and move ins as a positive number.

When you enter an incorrect value into one of the projection cells for the current week, a data validation error message comes up and you have the option of "retry" or "cancel."

However ,if you hit escape instead of "retry" or "cancel," then it unlocks the entire worksheet. Does anyone know why or how to stop this from happening.

I have looked through all my code, and there is nothing that would indicate this to happen. I have also researched this all over the internet and can't find an answer. This one has me stumped, I have never seen it before.

Is there a chance one of my worksheet level events is causing this?


Anyway, This is my first posting so I hope I'm not being too demanding or rude in any way. I just don't know where else to turn at this point.


Thanks so much,

 

[Demo8II]

I would just like to add, after reading the posting guidelines, that I am on windows xp excel 2003. I am happy to post what I have so far, but as this problem is a kind of quark with excel as opposed to a logical problem, I didn't see that to be necessary.

Again, thanks.

 

[Stormseed]

Hi, welcome to MrExcel.

I see that whenever the DataValidation message pops up, and when the user hit Escape, you see that the entire worksheet cells are unlocked thereby keeping the Protection of the worksheet intact. Do you use a password in order to Protect the worksheet ?

Could you please clarify if you have all the cells across the Worksheet, locked prior to setting the Protection of your worksheet ? Please let us know the steps you perform in order to check the Locked/Unlocked status of a worksheet.

 

[Peter_SSs]

Demo8II

Welcome to the MrExcel board!

I suspect that it is quite likely some of your event code could be contributing to this. Do you have any event code (eg Selection_Change or Worksheet_Change) that unprotects your sheet and re-protects it at the end?

It probably would help if you posted your code.

 

[Demo8II]

Thank you all so much for the quick reply. I am very grateful. I hope I can be of help one day.

Anyway, There is a worksheet for each community within a given district, and they are all the same format. There are highlighted projection cells as I mentioned, and after several days from the projection due date has passed I have the projection cells lock via VBA so nobody can change their old projections. In going through each worksheet, all the cells are formatted to be locked, with the exception of the current date projections cells. It is the code that unlocks or locks any given cell.

I have provided all the code at the worksheet level below. Please don't laugh, I am still learning VBA.

First there is (to my understanding) a code that names the worksheet tab whatever is the value in cell F8. Then whenever F5 is changed, via a now() function on another sheet and a macro that puts the new date into F5 on all sheets, then all the columns for future months hide themselves, and all previous months projections cells lock. The unprotect and protect subs are down at the bottom. The hide columns and Lock Cells subs have been shortened just to show what they are doing. Lastly, there is a reference to cell F4 that when "off" is written in there, it uncollapses all rows and I think turns all the events off.









Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call UnprotectAll
On Error Resume Next
Me.Name = Range("F8").Value
If Target.Address = "$F$5" Then


ActiveSheet.Calculate
Columns("J:DA").EntireColumn.Hidden = False

Call HideColumns
Call LockCells
Call ProtectAll

Else

ActiveCell.Activate


End If

If Range("F4").Value = "OFF" Then

If Columns("J:BP").EntireColumn.Hidden = True Then

Columns("J:BP").EntireColumn.Hidden = False

ActiveCell.Activate


End If

End If

End Sub





Sub LockCells()

'Lock Column J

If Range("J4").Value = 1 Then

Range("J15:J18").Locked = True
Else
Range("J15:J18").Locked = False
End If


If Range("J4").Value = 1 Then

Range("J20:J23").Locked = True
Else
Range("J20:J23").Locked = False
End If

End Sub




Sub HideColumns()
'January

If Range("N5").Value = 0 Then

Columns("J:Q").EntireColumn.Hidden = True
End If

'February

If Range("V5").Value = 0 Then

Columns("R:Y").EntireColumn.Hidden = True
End If

End Sub


Sub ProtectAll()

ActiveSheet.Protect Password:="Time Out Of Mind"
End Sub

Sub UnprotectAll()
ActiveSheet.Unprotect Password:="Time Out Of Mind"
End Sub




Thank you so much,

Demo8II

 

[Peter_SSs]

Demo8II

From what I can see, this code will unprotect your sheet whenever you enter anything in any cell except F5 (which is normally populated by another macro). So it doesn't appear to me to be related to pressing the ESC when attempting to enter an incorrect value in a Data Validation cell.

The very first line of your Worksheet_Change code unprotects the sheet (Call UnprotectAll). The only place the sheet gets re-protected is in this part:

If Target.Address = "$F$5" Then

    ' Other code here
    Call ProtectAll

Else

In other words, if 'Target' is anything other than F5, the sheet doesn't get re-protected.

As a quick fix, I think you could move the Call ProtectAll from where it is down to immediately above the End Sub of the Worksheet_Change code.

If you need further help, it would also be useful to know what cell(s) contain the Data Validation that you think is causing the problem and just what the Data Validation settings are.

 

[Demo8II]

Peter SSs,

Thank you so much. It worked!

I guess I have still quite a bit to learn about worksheet level events, but that really helped me quite a bit.


You are truly excellent.


Thank you,

Demo8II