asked on

Prompt for number using Input Box and VBA

(Question re. Excel 2003)
I am just learning how to program and my question is hopefully a simple one for someone with more experience:
When a user selects a certain macro, I would like them for them to immediately be prompted to enter a number. This number (an "Instock Percentage") is constant throughout the document, so it only needs to be input once. How can I prompt for this value and then refer to it in later calculations? As an example, the "1000000" in the calculation below is where the user-defined value should go.

ActiveCell.FormulaR1C1 = "=IF(RC[1]<1000000,RC[-1]/RC[1]*1000000,RC[-1])"

ASKER CERTIFIED SOLUTION

Jorge Paulino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jeverist

Hi Joshua_Mann,

> I am just learning how to program

1) I would advise against using an Integer data type. In fact, you will get an "Run-time error 6: Overflow" if you try to enter '1000000' using an Integer variable. (Integer can only hold numbers up to 32767). Use a Long type or better yet, Double.
2) Set the range (cell) you need the formula in instead of using ActiveCell since ActiveCell will change.
3) Use 'Application.InputBox' with 'Type:=1' to return a number.

Try this:

Sub GetNumber()

Dim cel As Range, dNumber As Double
dNumber = Application.InputBox(Prompt:="Enter Instock Percentage", Title:="Instock Percentage", Type:=1)

Set cel = Range("C4") ' Set this to the cell needed for the formula
cel.FormulaR1C1 = "=IF(RC[1]<" & dNumber & ",RC[-1]/RC[1]*" & dNumber & ",RC[-1])"

End Sub

Jim

Jorge Paulino

:-) That's right jeverist. Thanks for the correction!

jeverist

DanRollins,

Except for the variable type definition, jpaulino had the correct answer here: http:/Q_22732161.html#19602022. I recommend accept jpaulino.

Jim

Computer101

Forced accept.

Computer101
EE Admin