Joshua_Mann
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]*1000 000,RC[-1] )"
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]/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:-) That's right jeverist. Thanks for the correction!
DanRollins,
Except for the variable type definition, jpaulino had the correct answer here: http:/Q_22732161.html#19602022. I recommend accept jpaulino.
Jim
Except for the variable type definition, jpaulino had the correct answer here: http:/Q_22732161.html#19602022. I recommend accept jpaulino.
Jim
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
> 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(Promp
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