Parameter Query executed from a Macro
 

Hi,

I am trying to get a Macro, to execute a parameter query, that will prompt a user to enter a variable. Every time that I try to record my macro, my session abends. My query works perfectly, when i execute it independent of my macro.

Can I do this in Excel.

I know this is easy in Access.

Thank You very much

Parameter Query executed from a Macro
 

Hello,

One of my kind colleagues in this forum taught me how to hide an
unhide columns using a parameter query (run by a macro) by creatin
VBA. See the text in quotes below right after "InputBox".

Sub hidecolumns()
Columns("d:en").Hidden = False
x = InputBox("Enter 1 for DHS Recommendation. Enter 2 for PROJEC
Recommendation Enter 3 for FINAL Recommendation Enter 4 for Project/DH
Matches")
For Each c In Range("d1:en1")
If Right(c, 1) < x Then c.EntireColumn.Hidden = True
Next
End Sub

However, I have another question about this please.

I would like to be able to have the Enter 1, Enter2 text etc. eac
appear on a new line so that they are not jumbled up. See attached fil
and run the "Show some detail" macro. I suppose there is sme way t
insert a"new line" or "carriage return", but since I don't know VBA,
would appreciate any help. Thx.

lindas

Attachment filename: calwin fs training analysis 4-19-04-forum.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=51725
--
Message posted from http://www.ExcelForum.com

Parameter Query executed from a Macro
 

lindasf

It will be easier to manage if you put your prompt in a variable. Then use
vbNewLine to insert a carriage return.

Dim sPrompt As String

sPrompt = "Enter 1" & vbNewLine & "Enter2" & vbNewLine & "Enter3"

x=InputBox(sPrompt)

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

"lindasf " wrote in message
...
Hello,

One of my kind colleagues in this forum taught me how to hide and
unhide columns using a parameter query (run by a macro) by creating
VBA. See the text in quotes below right after "InputBox".

Sub hidecolumns()
Columns("d:en").Hidden = False
x = InputBox("Enter 1 for DHS Recommendation. Enter 2 for PROJECT
Recommendation Enter 3 for FINAL Recommendation Enter 4 for Project/DHS
Matches")
For Each c In Range("d1:en1")
If Right(c, 1) < x Then c.EntireColumn.Hidden = True
Next
End Sub

However, I have another question about this please.

I would like to be able to have the Enter 1, Enter2 text etc. each
appear on a new line so that they are not jumbled up. See attached file
and run the "Show some detail" macro. I suppose there is sme way to
insert a"new line" or "carriage return", but since I don't know VBA, I
would appreciate any help. Thx.

lindasf

Attachment filename: calwin fs training analysis 4-19-04-forum.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=517255
---
Message posted from http://www.ExcelForum.com/

Parameter Query executed from a Macro
 

David

It sounds like you already have a query in a worksheet. Is it a parameter
query? If so, you only need to refresh it to get a prompt with code like

Sheet1.QueryTables(1).Refresh

If it's not a parameter query, you can either make it one, or prompt the
user for a value using InputBox and adjust the SQL to reflect that new
value. See here

http://www.dicks-clicks.com/excel/Ex....htm#ChangeSQL

and here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

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

"David" wrote in message
...
Hi,

I am trying to get a Macro, to execute a parameter query, that will prompt
a user to enter a variable. Every time that I try to record my macro, my
session abends. My query works perfectly, when i execute it independent of
my macro.

Can I do this in Excel.

I know this is easy in Access.

Thank You very much

Parameter Query executed from a Macro
 

Thanks Dick. This works great!

lindas

--
Message posted from http://www.ExcelForum.com