I have a macro with references that will change month to month. I want to
setup an input box that will ask the user which reference (defined name) to
use and will insert it into the macro and have it continue from there.

What the macro does is copies a number of columns and pastes them into
another workbook including a column for the amount for the month. so the
line for february look like this:

Application.Goto Reference:="Submitted,Feb"

In the end, what I want to happen is for a message box to appear asking
"What month?" or what defined name (which are already set up for all months)
and the user would type in "Mar" and the line would change to:

Application.Goto Reference:="Submitted,Mar"

or however else it would do it.


Any suggestions?
Niq

The standard method (to allow user input, typed) would be
to use an input box instead of a message box:

RefStr = InputBox("Enter the reference you wish to use:")
Application.Goto Reference:="Submitted,Mar

Or, a better way since it avoids typos would be to create
a userform with a list or combo box with all your
predefined reference names - then in your VBA code call
the Show method of the user form when you need the input.

-----Original Message-----
I have a macro with references that will change month to
month. I want to
setup an input box that will ask the user which reference
(defined name) to
use and will insert it into the macro and have it
continue from there.

What the macro does is copies a number of columns and
pastes them into
another workbook including a column for the amount for
the month. so the
line for february look like this:

Application.Goto Reference:="Submitted,Feb"

In the end, what I want to happen is for a message box to
appear asking
"What month?" or what defined name (which are already set
up for all months)
and the user would type in "Mar" and the line would
change to:

Application.Goto Reference:="Submitted,Mar"

or however else it would do it.


Any suggestions?
Niq


.

K Dales

That sounds like a great idea. Unfortunately I'm such a beginner at this, I
wouldnt know where to start.

Could you give me an idea on how to do that?

Thanks
Dominique

"K Dales" wrote in message
...
The standard method (to allow user input, typed) would be
to use an input box instead of a message box:

RefStr = InputBox("Enter the reference you wish to use:")
Application.Goto Reference:="Submitted,Mar

Or, a better way since it avoids typos would be to create
a userform with a list or combo box with all your
predefined reference names - then in your VBA code call
the Show method of the user form when you need the input.

-----Original Message-----
I have a macro with references that will change month to
month. I want to
setup an input box that will ask the user which reference
(defined name) to
use and will insert it into the macro and have it
continue from there.

What the macro does is copies a number of columns and
pastes them into
another workbook including a column for the amount for
the month. so the
line for february look like this:

Application.Goto Reference:="Submitted,Feb"

In the end, what I want to happen is for a message box to
appear asking
"What month?" or what defined name (which are already set
up for all months)
and the user would type in "Mar" and the line would
change to:

Application.Goto Reference:="Submitted,Mar"

or however else it would do it.


Any suggestions?
Niq


.

Limited here by space somewhat - I will give a few basics,
if you want to know more email me (figure the address from
the following):

To create a userform: Go to the Visual Basic Editor.
From the Insert menu, choose "UserForm."

To add controls (I would suggest a combobox containing the
names of your predefined references): Make sure the
Toolbox is showing (View menu) and select the control you
want from it. Then use your mouse to outline a "box" on
the userform - the control will be added to the form in
that box.

Right-click on the control now and choose "Properties" to
set up the control - e.g., to enter your list of choices
(the "RowSource" property in this case). You will need to
refer to help or the MSDN library here, since this gets
rather involved.

Once the userform is set up, you can use a Visual Basic
routine to display it using its Show method:

UserForm1.Show

You can refer to the value selected from the Combobox in
your Visual Basic code like this:

ReferenceStr = UserForm1.Controls("Combobox1").Value

Hope this at least gives you an idea where to go...

Ken Dales



-----Original Message-----
K Dales

That sounds like a great idea. Unfortunately I'm such a
beginner at this, I
wouldnt know where to start.

Could you give me an idea on how to do that?

Thanks
Dominique

"K Dales" wrote in
message
...
The standard method (to allow user input, typed) would
be
to use an input box instead of a message box:

RefStr = InputBox("Enter the reference you wish to
use:")
Application.Goto Reference:="Submitted,Mar

Or, a better way since it avoids typos would be to
create
a userform with a list or combo box with all your
predefined reference names - then in your VBA code call
the Show method of the user form when you need the
input.

-----Original Message-----
I have a macro with references that will change month
to
month. I want to
setup an input box that will ask the user which
reference
(defined name) to
use and will insert it into the macro and have it
continue from there.

What the macro does is copies a number of columns and
pastes them into
another workbook including a column for the amount for
the month. so the
line for february look like this:

Application.Goto Reference:="Submitted,Feb"

In the end, what I want to happen is for a message box
to
appear asking
"What month?" or what defined name (which are already
set
up for all months)
and the user would type in "Mar" and the line would
change to:

Application.Goto Reference:="Submitted,Mar"

or however else it would do it.


Any suggestions?
Niq


.



.