How do I obtain user input while running a macro

[Dave]

I have created a macro that updates several Project fields and then outputs
to an Access database. In one of the fields that needs to be updated I would
like to have the user input the date value for use in the calculation. I
would also like the user to specify which Access database to output the
information.

Any suggestions and guidance is greatly appreciated.
Thanks, Dave M.

 

[Mike Glen]

Hi Dave,

Try posting on the developer newsgroup. Please see FAQ Item: 24. Project
Newsgroups. FAQs, companion products and other useful Project information
can be seen at this web address: http://www.mvps.org/project/.

Mike Glen
Project MVP

 

[Mark Durrenberger]

Create a form and capture the information on the form, check it for proper
formatting then pass it to project.

We'll be able to help with the next steps as well but post them in the
"developer" newsgroup

microsoft.public.project.developer

To create a form go to the VBA Editor (Alt-f11) right click on
"projectglobal" and insert userform...
then you can add text boxes (input) or labels (instructions for the user).
Your code will have to show.formname
and hide.formname

and all the stuff in-between. I'm sure there is more help on the web - try
the mvp web site

http://www.mvps.org/project/

Mark



--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[John]

Dave,
Unless I have several user input values, I simply use either a MsgBox
function (simple "yes" or "no" type input) or an InputBox function
(allows direct user input). I only use a userform for more complex user
interaction (multiple user selections or inputs). The userform is more
complex to set up but it has more flexibility. In your case for two
relatively simple user inputs, a couple of InputBox functions should
work fine.

One nice thing about the functions versus a userform, the functions can
be inserted anywhere in the code. That allows them to be called up after
the code has already done some operations. Whereas, a userform can only
be used at the beginning of a macro (i.e. the userform pops up, the user
provides inputs, and then the main VBA macro runs).

John

 

[Mark Durrenberger]

Actually, you can use a user form anywhere in code - you just need to know
how to call it and how to reference values obtained from the user...

a msgbox is just a basic user form that hides most of the programming....

Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[Dave]

Hi Mark,

Thanks for the help. I created a form with a calendar for the user to select
a date. How do I put the calendar date value selected in the date field (in
my case Date10) of the Project file? Do I set the formula property of the
Date10 field?

Thanks again, Dave

 

[John]

Mark,
Ok, enlighten me. Let's say I start a macro and it executes several
lines of code. Then I call a userform in the middle of the main macro.
How do I get the main macro code to pick up where it left off and run
the rest of its code after the user made selections on the userform?

John

 

[Mark Durrenberger]

sub test()
dim blnFlag as boolean
dim b as string
do some thing that sets blnFlag to true or false
if blnFlag then
frmMyForm1.show (vbmodal) ' a form I've created
b = frmMyForm1.text1 ' on form 1 is a text box named "text1"
else
frmMyForm2.show (vbmodal) ' another form I've created
b = frmMyForm2.text1 ' on form 2 is a text box named "text1"
endif

end sub

This sub could be run from a form or tied to a button or put in the on-open
event etc...

Regards
Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[John]

Mark,
I see what the example does but it doesn't answer my question. I am
looking for a way to run code as follows:
1. Start macro #1
2. Run half (or some part) of the code in macro #1 and then call a
userform
3. Show the userform and wait for user input
4. When user input is complete transfer execution back to macro #1 and
run the remaining code of macro #1

The example you provided emulates steps 1 through 3 but it doesn't do
step 4, that is, transfer back to the original macro and pick up
execution where it left off in step 2.

Hopefully there is a way to do what I want but I haven't been able to
figure it out although I did figure out a workaround. Namely, break the
main macro into two macros with the userform in between.

Still wondering.
John

 

[Mark Durrenberger]

sub Macro 1( )

dim myDate as string

do stuff

' now display your user form

frmForm1.show (vbmodal) ' Modal forms stop execution of the calling routine
and wait for user input

' let's assume that on frmForm1 we have a calendar control called
"calendar1" and an OK command button called cmdOK
' on that calendar control, the user selects a date, then clicks on an "OK"
button
' in the cmdOK_Click event put
' myDate = calendar1.day '(I've never used the calendar control so check
this)
' follow this with frmForm1.hide (or Me.hide works too) to hide the form and
return control to macro1

do more stuff

' clean up
set frmForm1 = nothing ' releases memory used by form1
end sub

HTH,
Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[John]

Mark,
I thought I looked at that approach and for some reason decided it
wouldn't do what I needed but it is certainly worth another go. Thanks
for the input.

John

 

[Sarah]

John,

You may want to look into using a "Resume Line" statement.

Sarah

 

[Mark Durrenberger]

This is more of a background question, but is it possible to get all the
user input BEFORE running any of your code?
That means calling a form, getting and validating user input on the form
then launching your code from an OK button click event.

This would be the most efficient approach...

What does your code do?

Mark


--
_________________________________________________________
Mark Durrenberger, PMP
Principal, Oak Associates, Inc, www.oakinc.com
"Advancing the Theory and Practice of Project Management"
________________________________________________________

The nicest thing about NOT planning is that failure
comes as a complete surprise and is not preceded by
a period of worry and depression.

- Sir John Harvey-Jones

 

[John]

Mark,
To be honest I kinda forgot exactly why I wanted to pop up a userform in
the middle of the macro but I think it had to do with getting a simple
initial response from the user via a MsgBox function and then depending
on the initial input, pop up a userform if more complex input was
required. For example, try this out. Let's say the user has displayed a
filtered Resource Usage view and now the user selects non-contiguous
assignments (no resources, just some assignments) on that filtered view.
Now figure out which assignments they selected and export the timescale
data to Excel. Oh by the way, you also need to figure out which
resources are associated with those selected assignments so the selected
assignment data can be exported to Excel . . . sorted alphanumerically
and shown by resource. At least initially, it turned out the requirement
wasn't quite that challenging so I have been able to use a simple MsgBox
for all the user input (i.e. does the user want Work or Cost data
exported).

I have only created one userform so far (different macro) although it
was rather complex. And now that I look back at it I did actually run
some code to first check if indeed a project file was open and that it
had at least one task. If that was ok, the setup macro called the
userform to obtain several pieces of information (options) from the
user. The userform stayed active while the rest of the macro ran because
it also displayed progress. In this case, once the user data was
gathered and the user hit "ok", the userform called the main processing
macro that gathered data based on options selected by the user,
manipulated the data (the gist of the macro is a standard calendar to
financial calendar converter), and then exported the financial calendar
data to Excel, again based on options selected by the user on the
userform. In this case the, macro #1, followed by a userform, followed
by macro #2, approach worked great.

What I was looking for with our current discussion (although I don't
think I need it anymore) was to be able to use just one macro with a
userform in the middle and you offered the simple suggestion to hide
(i.e. release) the userform once the user gave their input and hit "ok".
That approach should work fine as long as I don't need to keep the
userform open to show progress.

Now you probably have more information than you really wanted to know. I
think it's more than I want to know.

John

 

[John]

Sarah,
Thanks for the input but I don't think it will work. It seems the Resume
Line statement would have to be placed in the code for the userform but
I don't see how that would transition to the main macro code.

I could also use a flag that starts out "false" (default) but gets set
to "true" in the userform. Then when the userform calls the main macro
again (basically restarting it), the value of the flag is transferred
and causes the main macro to jump ahead to the code following where the
userform was called. A bit kludgy, but it will work.

You might like to read my latest response to Mark. It fills in a few
more details.

John