Reference message box

  • Thread starter Thread starter Dominique Feteau
  • Start date Start date
D

Dominique Feteau

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.
 
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
 
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): kgdcc{NOtoSPAM!}@westelcom.com

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
 
Back
Top