Protect Sheet - Data Validation Not Working

[The_Steward]

Hello

I am using a dependent searchable drop down data validation list in my excel sheet. But the list does not drop down immediately when the sheet is protected (Is usually delayed by up to a few minutes after I click on another cell and re-click the cell). Why is this happening and how do I fix this?

The formula I use for the dependent searchable drop down data validation list is not a macro but there is a macro connected with that cell so that it expands when selected.

 

[6StringJazzer]

Please give the details on how your dependent searchable drop down data validation list is set up. Is the cell unlocked?

 

[The_Steward]

Cell is unlocked and linked to cell in another sheet Which has following formula... =IF('Sheet1'!C11=$R$2,FILTER($R$3:$R$162,ISNUMBER(SEARCH('Sheet1'!C12,$R$3:$R$162)),"Not Found"),IF('Sheet1'!C11=$S$2,FILTER($S$3:$S$162,ISNUMBER(SEARCH('Sheet1'!C12,$S$3:$S$162)),"Not Found")

C12 is cell in question
Content of C11 is what C12 is dependent upon

Both sheets are automatically locked by Macro upon opening workbook

 

[nihad]

Hi

I'm facing the same issue, did you find a solution?

Thanks

 

[The_Steward]

Hi

I'm facing the same issue, did you find a solution?

Thanks

Yes I did! Using VBA I had to unlock the sheet when the cells with dropdowns were selected and then lock the the sheet once a cell outside the range was selected. The problem was that dropdown would not work if column was being expanded and the sheet was protected. Hope this helps.

Below is exactly what i'm using in my VBA atm. Do not forget to add the starting command and end sub.

If Target.Count > 1 Then Exit Sub

If Not Application.Intersect(Target, Range("")) Is Nothing Then

ActiveSheet.Unprotect Password:=""

Target.Columns.ColumnWidth = ""



Else

ActiveSheet.Unprotect Password:=""

Columns("H:L").ColumnWidth = ""

ActiveSheet.Protect Password:=""

End If

 

[nihad]

o thank you very much

and I'm assuming this isn't possible to do it without VBA?

 

[The_Steward]

o thank you very much

and I'm assuming this isn't possible to do it without VBA?

If you're changing the columns or rows dimensions and the sheet is protected then no. Or at least not that i'm aware of. My formula for the dropdown worked fine when I was not trying to change the columns dimensions or I had the sheet unprotected.

 

[nihad]

If you're changing the columns or rows dimensions and the sheet is protected then no. Or at least not that i'm aware of. My formula for the dropdown worked fine when I was not trying to change the columns dimensions or I had the sheet unprotected.

I'm using Filter function

 

[nihad]

If you're changing the columns or rows dimensions and the sheet is protected then no. Or at least not that i'm aware of. My formula for the dropdown worked fine when I was not trying to change the columns dimensions or I had the sheet unprotected.

I got it to work:

=TRANSPOSE(SORT(UNIQUE(FILTER(Table25,ISNUMBER(SEARCH('Recharge Sheet'!G5,Table25,1)),"Account not found"))))