Prompt for number using Input Box and VBA

Posted on 2007-07-31
Last Modified: 2013-12-26
(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])"

Question by:Joshua_Mann
    LVL 48

    Accepted Solution

    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
    LVL 38

    Expert Comment

    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

    LVL 48

    Expert Comment

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

    Expert Comment


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

    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now