Hi,

I am building a spreadsheet which has an external query which should
refresh when a parameter changes.

The sheet is being used as a form by users so I want to protect the
sheet and have the query hidden - the query result feeds valid entries
on another cell.

The problem I have is that, with the query setup to refresh when the
paramter changes, it is unable to do so as the sheet is protected.

Can anyone tell me the vb I can use to unprotect the sheet, refresh the
query, then protect the sheet when a particular value changes.

Note that there are multiple cells and queries on the same sheet that I
need to be able to do this with.

Many thanks,

Gavin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Gavin

You can use the Worksheet_Change event. Right click on the sheet tab and
choose View Code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim qt As QueryTable

Application.EnableEvents = False
Me.Unprotect

For Each qt In Me.QueryTables
If qt.Parameters.Count = 1 Then
If qt.Parameters(1).Type = xlRange Then
If Not Intersect(qt.Parameters(1).SourceRange, Target) _
Is Nothing Then

qt.Refresh False
End If
End If
End If
Next qt

Application.EnableEvents = True
Me.Protect

End Sub

This should work for all your cells and all your queries. It loops through
all the queries and if the query has a range paramter, it checks to see if
the changed cell is in that parameter. If so, if refreshes the query.

You have to make sure that you UNCHECK Refresh Query... in the Parameters
dialog, or you will get a protection error. Test it out and let me know if
you run into problems.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"gavjs" wrote in message
...
Hi,

I am building a spreadsheet which has an external query which should
refresh when a parameter changes.

The sheet is being used as a form by users so I want to protect the
sheet and have the query hidden - the query result feeds valid entries
on another cell.

The problem I have is that, with the query setup to refresh when the
paramter changes, it is unable to do so as the sheet is protected.

Can anyone tell me the vb I can use to unprotect the sheet, refresh the
query, then protect the sheet when a particular value changes.

Note that there are multiple cells and queries on the same sheet that I
need to be able to do this with.

Many thanks,

Gavin



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/