Hi all,

Is it possible to include the result of a formula as part of the
message in an InputBox?

This is what I need to do:

1. I'm designing a spreadsheet which randomly selects the name of a
ship from a list.
2. Having performed that, the user then needs to input the number of
crew on the selected ship - this is done via an InputBox in VBA.
3. However, the user needs to know what ship has been selected, so I
would like that to be displayed as part of the InputBox message - is
that possible?

The random selection is done using =RANDBETWEEN, as all the ships have
a number as well as a name: these are stored as two columns in the
worksheet and the random function uses the lower and upper numbers of
the ships. A vlookup is then performed to derive the ship's name.

I'd be quite happy if the InputBox statement is performed after the
random selection and the vlookup are done, thus leaving the ship's
name in a cell - then, (if it can be done), to refer to the contents
of that cell as part of the InputBox message, to create something
like.....

"The ship selected is "& A1 &" Please enter the crew complement for
this ship"

.... where cell A1 would contain the vlookup formula

I've tried various ways before posting this, but so far come up blank
- perhaps someone can tell me if it is indeed possible in VBA before I
lose the will to live!

Many thanks

Phil

On Apr 12, 12:58 am, "Phil" wrote:
Hi all,

Is it possible to include the result of a formula as part of the
message in an InputBox?

This is what I need to do:

1. I'm designing a spreadsheet which randomly selects the name of a
ship from a list.
2. Having performed that, the user then needs to input the number of
crew on the selected ship - this is done via an InputBox in VBA.
3. However, the user needs to know what ship has been selected, so I
would like that to be displayed as part of the InputBox message - is
that possible?

The random selection is done using =RANDBETWEEN, as all the ships have
a number as well as a name: these are stored as two columns in the
worksheet and the random function uses the lower and upper numbers of
the ships. A vlookup is then performed to derive the ship's name.

I'd be quite happy if the InputBox statement is performed after the
random selection and the vlookup are done, thus leaving the ship's
name in a cell - then, (if it can be done), to refer to the contents
of that cell as part of the InputBox message, to create something
like.....

"The ship selected is "& A1 &" Please enter the crew complement for
this ship"

... where cell A1 would contain the vlookup formula

I've tried various ways before posting this, but so far come up blank
- perhaps someone can tell me if it is indeed possible in VBA before I
lose the will to live!

Many thanks

Phil

Hi Phil,

It does work, try...

"The ship selected is " & Range("A1").Value &". Please enter the crew
complement for this ship"

for the inputbox prompt.

Ken Johnson

"The ship selected is "& Range("A1").Value & vbNewLine & _
"Please enter the crew complement for this ship"


--
HTH

Bob

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

"Phil" wrote in message
oups.com...
Hi all,

Is it possible to include the result of a formula as part of the
message in an InputBox?

This is what I need to do:

1. I'm designing a spreadsheet which randomly selects the name of a
ship from a list.
2. Having performed that, the user then needs to input the number of
crew on the selected ship - this is done via an InputBox in VBA.
3. However, the user needs to know what ship has been selected, so I
would like that to be displayed as part of the InputBox message - is
that possible?

The random selection is done using =RANDBETWEEN, as all the ships have
a number as well as a name: these are stored as two columns in the
worksheet and the random function uses the lower and upper numbers of
the ships. A vlookup is then performed to derive the ship's name.

I'd be quite happy if the InputBox statement is performed after the
random selection and the vlookup are done, thus leaving the ship's
name in a cell - then, (if it can be done), to refer to the contents
of that cell as part of the InputBox message, to create something
like.....

"The ship selected is "& A1 &" Please enter the crew complement for
this ship"

... where cell A1 would contain the vlookup formula

I've tried various ways before posting this, but so far come up blank
- perhaps someone can tell me if it is indeed possible in VBA before I
lose the will to live!

Many thanks

Phil

On 11 Apr, 16:22, "Bob Phillips" wrote:
"The ship selected is "& Range("A1").Value & vbNewLine & _
"Please enter the crew complement for this ship"

--
HTH

Bob

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

"Phil" wrote in message

oups.com...



Hi all,

Is it possible to include the result of a formula as part of the
message in an InputBox?

This is what I need to do:

1. I'm designing a spreadsheet which randomly selects the name of a
ship from a list.
2. Having performed that, the user then needs to input the number of
crew on the selected ship - this is done via an InputBox in VBA.
3. However, the user needs to know what ship has been selected, so I
would like that to be displayed as part of the InputBox message - is
that possible?

The random selection is done using =RANDBETWEEN, as all the ships have
a number as well as a name: these are stored as two columns in the
worksheet and the random function uses the lower and upper numbers of
the ships. A vlookup is then performed to derive the ship's name.

I'd be quite happy if the InputBox statement is performed after the
random selection and the vlookup are done, thus leaving the ship's
name in a cell - then, (if it can be done), to refer to the contents
of that cell as part of the InputBox message, to create something
like.....

"The ship selected is "& A1 &" Please enter the crew complement for
this ship"

... where cell A1 would contain the vlookup formula

I've tried various ways before posting this, but so far come up blank
- perhaps someone can tell me if it is indeed possible in VBA before I
lose the will to live!

Many thanks

Phil- Hide quoted text -

- Show quoted text -

Thanks Bob and Ken - both work a treat, and exactly what I needed.

Best wishes,

Phil

You're welcome Phil.
Thanks for the feedback.

Ken Johnson