refresh with power query sheets that are protected

[rjmdc]

i cannot refresh since it is protected
is there a way to use code to do a refresh anyway and keep sheet locked?

 

[alansidman]

You will need to unlock and refresh and then re-lock. Here is a link that guides you

How to allow external data refresh in protected worksheet in Excel?

www.extendoffice.com

 

[rjmdc]

is there code that will upon pressing refresh unlock, refresh and relock?

 

[alansidman]

Did you bother to read the link attached. It is very clear how to do this.

 

[rjmdc]

i did and i tried to copy the formula
it describes the end result making a separate "search bar" on another sheet to use as the reference for lookups

i have a data sheet that needs to populate column K with a dropdown list - i did the data validation part
how do i use the videos search bar to enter the information all down column K

 

[rjmdc]

sorry
i responded incorrectly
please ignore

 

[rjmdc]

the code requests an external lookup sheet
the query is based on 7 sheets
how do i allow the refresh all and then relock