Intersect, Target code does not work on protected sheet

[mdd16]

Hello,

I have encountered a problem that has made me very disappointed at end of a huge project.

I have created intersect, target code for changing in-cell drop down lists for Data validation. I am changing contents of drop down list based on the prior cell value. It has been working like a charm on my computer. However for handing out file copies to others, I need to password protect the sheet.

When I am putting password protection on the sheet the intersect, target procedures, which I have written for the sheet stop working..

Have I reached end of the road .. or there is a way to solve this..


thanks for your attention.. Any help will be appreciated very much..

 

[jasonb75]

It should work fine if you unprotect the sheet at the start of the procedure, then reprotect it at the end, or you could use the UserInterfaceOnly: = True method in a workbook open event so that vba can access your sheets as if they were not protected while still restricting the access of the users.

 

[mdd16]

Thanks a lot Jason. You got my excited for a while..

Unfortunately, the UserInterfaceOnly: = True method did not make it work too.. Looks like there are some serious limitations of UserInterfaceOnly: = True method.. as discussed in other threads.

I have even tried to unprotect and protect the sheet in the code. However for the code to trigger upon value change in the cell, the sheet must first be unlocked. To plan a work around my dynamically altering drop down lists will be huge rework..

But none the less, thank you for your effort towards my problem...

 

[jasonb75]

Without seeing the code and the affected sheet, i.e. which cell change triggers the code and which protected area is causing the problem, I think that it is going to be very difficult to diagnose.

However for the code to trigger upon value change in the cell, the sheet must first be unlocked.

Again, without seeing the procedure in action this is not an accurate diagnosis, but that sounds more like things being done in the wrong order.

If the change is being triggered by user interaction then the cell needs to be unlocked so there is no reason for intersect to fail, if the change is being made by vba then it could be the secondary triggering that is the problem.

 

[mdd16]

Hello Jason, thanks for your attention. I have already extracted the affected portion of the file into a new workbook. can I send to you for checking. The worksheet will have the code also.

I am triggering 4-5 cells one after the other in a row and depending on contents of each cell I am changing the drop down contents of the neighbouring cell on the right in the same row.

Relevant cells are G47:K55 in the spreadsheet.


how do I send to you ? I only see options to upload image here. One option is for you to download from this link here from Dropbox

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[jasonb75]

Which part is not working correctly? I've tried some of the dropdowns in G47:O55 and they all appear to be functioning as I would expect.

 

[mdd16]

I forgot to mention. In the code I am unprotecting the sheet and therefore they seem to work fine. If I protect the sheet at the end of code it stops working.

Please try get the updated copy again. Now I have protected the sheet at the end of the code.

 

[jasonb75]

It appeared to be the same, but I did notice that you were unprotecting again at the end, changing it to protect is deleting the old validation, but not putting the new list back. Is this the same problem that you were seeing?

 

[mdd16]

It appeared to be the same, but I did notice that you were unprotecting again at the end, changing it to protect is deleting the old validation, but not putting the new list back. Is this the same problem that you were seeing?

Yes exactly. The drop down lists stop populating after protection

 

[jasonb75]

Following up, it looks like the code is looping because you haven't disabled events, although I'm not entirely sure why it it is behaving differently when the sheet is protected.

Making this minor edit to the code to prevent it from looping when the adjacent columns are cleared appears to work correctly, although there may be other problems that I have not found.
You will still need the lines to unprotect at the start and reprotect at the end.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    ' rest of code goes here

Application.EnableEvents = True
End Sub