Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3639
  • Last Modified:

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



0
Joshua_Mann
Asked:
Joshua_Mann
  • 2
  • 2
1 Solution
 
jpaulinoCommented:
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
 
jpaulinoCommented:
:-) 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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now