Protect Sheet - Data Validation Not Working

The_Steward

New Member
Joined
Nov 26, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
Please give the details on how your dependent searchable drop down data validation list is set up. Is the cell unlocked?
 

The_Steward

New Member
Joined
Nov 26, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi

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

Thanks
 

The_Steward

New Member
Joined
Nov 26, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
o thank you very much

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

The_Steward

New Member
Joined
Nov 26, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 24, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,130,014
Messages
5,639,555
Members
417,098
Latest member
steverob

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
Top