Refresh query table on protected worksheet

[jcaptchaos2]

I have a query table that is pulling data from our main frame, parameters option so the person can insert a number into a cell and the data in the table updates. I want to protect the worksheet an leave the cell that they type in unprotected. I formatted the cell unprotected so you can type into it but the table does not update. Is there a way that I can protect the worksheet and still have the data update?

 

[Smitty]

If you refresh the query via code, you can either set UserInterfaceOnly:=True or unprotect the sheet, run the query, then reprotect.

HTH,

 

[jcaptchaos2]

Smitty, thanks I didn't even think of that, I will do that but another question, I know how to do that via creating a macro and using a button but is there a way to set the code to the sheet so if the cell changes it runs the macro?

 

[Smitty]

Sure, you can use a Change event. Here's some boiler-plate code for it:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[jcaptchaos2]

Thanks Smitty I will give it a try