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])"



Joshua_MannAsked:
Who is Participating?
 
Jorge PaulinoIT Pro/DeveloperCommented:
Dim intValue As Interger
intValue  = InputBox("Enter the number)

If Isnumeric(intValue) Then
 ActiveCell.FormulaR1C1 = "=IF(RC[1]<" & intValue  & ",RC[-1]/RC[1]*" & intValue & ",RC[-1])"
End If
0
 
jeveristCommented:
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
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
:-) That's right jeverist. Thanks for the correction!
0
 
jeveristCommented:
DanRollins,

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

Jim
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.