I was able to make Excel pop-up a parameter dialogue box by creating
macro and changing the "SELECT ... " statement by adding the "WHER
field = ?" clause. However, it does not give a descriptive prompt
something like "Enter the branch code". In MS Query, it work
something like this: "SELECT ... WHERE branch = [Enter the branc
code]

--
bcha
-----------------------------------------------------------------------
bchan's Profile: http://www.excelforum.com/member.php...nfo&userid=827
View this thread: http://www.excelforum.com/showthread.php?threadid=27008

bchan

Parameters for PivotCache queries are not supported. You can trick it by
changing the SQL and putting in the ?, but there's no access to a Parameter
object so you can't change the prompt string. You can either roll your own
as amo suggested or create the QueryTable and PivotTable separately. The QT
can have a customize parameter and you can base the PT on that range.
Refreshing the PT doesn't automatically refresh the QT, which stinks.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"bchan" wrote in message
...

I was able to make Excel pop-up a parameter dialogue box by creating a
macro and changing the "SELECT ... " statement by adding the "WHERE
field = ?" clause. However, it does not give a descriptive prompt,
something like "Enter the branch code". In MS Query, it works
something like this: "SELECT ... WHERE branch = [Enter the branch
code]"


--
bchan
------------------------------------------------------------------------
bchan's Profile:
http://www.excelforum.com/member.php...fo&userid=8274
View this thread: http://www.excelforum.com/showthread...hreadid=270081