I want to be able to write a macro that when run looks at a user entry
(numeric) and based on that answer, runs a specific corresponding
macro.

Example:

User open the workbook and answers the prompted question with a 1 and
clicks the "submit" button. The macro looks at the answer (the 1) and
opens another workbook and runs macro 1. If however the user entered 9
then the macro would open the workbook and run macro 9.

Is there a way to do this?

I know how to open another workbook and run a macro from a macro. The
part I don't know is how to have the macro decide which macro in the
other workbook to run.

Any assistance is greatly appreciated.

Don

ans = InputBox("Supply a number")
Select Case ans
Case 1
Workbooks.Open C"\book1.xls
Application.Run "'Book1.xls'!macro1"
Case 2
Workbooks.Open C"\book2.xls
Application.Run "'Book1.xls'!macro2"
'etc.
End Select

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
oups.com...
I want to be able to write a macro that when run looks at a user entry
(numeric) and based on that answer, runs a specific corresponding
macro.

Example:

User open the workbook and answers the prompted question with a 1 and
clicks the "submit" button. The macro looks at the answer (the 1) and
opens another workbook and runs macro 1. If however the user entered 9
then the macro would open the workbook and run macro 9.

Is there a way to do this?

I know how to open another workbook and run a macro from a macro. The
part I don't know is how to have the macro decide which macro in the
other workbook to run.

Any assistance is greatly appreciated.

Don

On Oct 30, 1:39 pm, "Bob Phillips" wrote:
ans = InputBox("Supply a number")
Select Case ans
Case 1
Workbooks.Open C"\book1.xls
Application.Run "'Book1.xls'!macro1"
Case 2
Workbooks.Open C"\book2.xls
Application.Run "'Book1.xls'!macro2"
'etc.
End Select

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

oups.com...



I want to be able to write a macro that when run looks at a user entry
(numeric) and based on that answer, runs a specific corresponding
macro.

Example:

User open the workbook and answers the prompted question with a 1 and
clicks the "submit" button. The macro looks at the answer (the 1) and
opens another workbook and runs macro 1. If however the user entered 9
then the macro would open the workbook and run macro 9.

Is there a way to do this?

I know how to open another workbook and run a macro from a macro. The
part I don't know is how to have the macro decide which macro in the
other workbook to run.

Any assistance is greatly appreciated.

Don- Hide quoted text -

- Show quoted text -

Bob,

You are going to think I'm an idiot but how does the macro know where
to pick the answer up from. I see you reference InputBox. What is
that or how to I the two together.

Perhaps it would help if i explained.

I run a daily report. When the user indicates the are completing the
report for Monday, it prompts to determine if they are beginning a new
business week. If they answer yes, it takes them to another page
which asks them to input some data. One of the questions they have to
answer is which fiscal year week are they running. This answer can be
anywhere from 1 to 52. Based on the answer they supply in that one
cell, I need to be able to run the macro for that week. There are a
number of other questions on the page but I need the macro to key to a
single answer.

I think I understand the rest of the macro, I just don't know how to
tie the InputBox reference to the correct cell.

Sorry for being so slow.

Maybe I misunderstood you, but you said wanted to look at user entry, which
I took to be some sort of active dialog, hence the Inputbox.



In this follow-up, you say it prompts for a new business week. Surely you
are using Inputbox for this, so you know how to integrate it.



If not, how are you determining the answers to all of these questions.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
ups.com...
On Oct 30, 1:39 pm, "Bob Phillips" wrote:
ans = InputBox("Supply a number")
Select Case ans
Case 1
Workbooks.Open C"\book1.xls
Application.Run "'Book1.xls'!macro1"
Case 2
Workbooks.Open C"\book2.xls
Application.Run "'Book1.xls'!macro2"
'etc.
End Select

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

oups.com...



I want to be able to write a macro that when run looks at a user entry
(numeric) and based on that answer, runs a specific corresponding
macro.

Example:

User open the workbook and answers the prompted question with a 1 and
clicks the "submit" button. The macro looks at the answer (the 1) and
opens another workbook and runs macro 1. If however the user entered 9
then the macro would open the workbook and run macro 9.

Is there a way to do this?

I know how to open another workbook and run a macro from a macro. The
part I don't know is how to have the macro decide which macro in the
other workbook to run.

Any assistance is greatly appreciated.

Don- Hide quoted text -

- Show quoted text -

Bob,

You are going to think I'm an idiot but how does the macro know where
to pick the answer up from. I see you reference InputBox. What is
that or how to I the two together.

Perhaps it would help if i explained.

I run a daily report. When the user indicates the are completing the
report for Monday, it prompts to determine if they are beginning a new
business week. If they answer yes, it takes them to another page
which asks them to input some data. One of the questions they have to
answer is which fiscal year week are they running. This answer can be
anywhere from 1 to 52. Based on the answer they supply in that one
cell, I need to be able to run the macro for that week. There are a
number of other questions on the page but I need the macro to key to a
single answer.

I think I understand the rest of the macro, I just don't know how to
tie the InputBox reference to the correct cell.

Sorry for being so slow.