+ Reply to Thread
Results 1 to 8 of 8

Receive "$" and "," in an InputBox

Hybrid View

  1. 11-04-2015, 07:32 PM #1
    ksayet
    ksayet is offline
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Receive "$" and "," in an InputBox

    Hi Guys!

    I'm having trouble validating the following InputBox. The InputBox is asking for a revenue number which will be plugged into a cell. In anticipation of the user entering one of the following, how can I ensure that only the number gets entered into the cell.

    Ex. $1,000, $1000, 1,000, 1000.00.

    Currently, the only type that doesn't give me a mismatch is "1000".

        Dim NetRevenue As Variant
    NetRevenue = Val(InputBox("What was weekly revenue?", "Net Food and Beverage Revenue", "$"))
    Sheets(1).Range("C1").Value = NetRevenue
    Reply With QuoteRegister To Reply
  2. 11-04-2015, 07:34 PM #2
    Obsessed
    Obsessed is offline
    Forum Contributor Obsessed's Avatar
    Join Date
    05-22-2013
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 365
    Posts
    215

    Re: Receive "$" and "," in an InputBox

    Not sure if there is a better option, but you could just do a double substitute and replace the $ and , with ""s.
    Want to show appreciation for the help you received from a member? Give them reps by clicking the bottom left of their post!
    Reply With QuoteRegister To Reply
  3. 11-04-2015, 08:01 PM #3
    ksayet
    ksayet is offline
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Receive "$" and "," in an InputBox

    how do you do a double substitute? sorry for my ignorance..
    Reply With QuoteRegister To Reply
  4. 11-04-2015, 07:43 PM #4
    stnkynts
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Receive "$" and "," in an InputBox

    Remove the Val. Excel should interpret the results as currency and act accordingly
    If you are happy with my response please click the * in the lower left of my post.
    Reply With QuoteRegister To Reply
  5. 11-04-2015, 08:01 PM #5
    ksayet
    ksayet is offline
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Receive "$" and "," in an InputBox

    It doesn't interpret $ or , correctly. I get a mismatch message.
    Reply With QuoteRegister To Reply
  6. 11-04-2015, 08:13 PM #6
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Receive "$" and "," in an InputBox

    just do it twice
    Dim NetRevenue As Variant
    NetRevenue = InputBox("What was weekly revenue?", "Net Food and Beverage Revenue", "$")
    NetRevenue = Replace(NetRevenue, "$", "")
    NetRevenue = Replace(NetRevenue, ",", "")
    Sheets(1).Range("C1").Value = NetRevenue
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  7. 11-05-2015, 02:20 PM #7
    ksayet
    ksayet is offline
    Registered User
    Join Date
    11-23-2013
    Location
    new york, ny
    MS-Off Ver
    Excel 2010
    Posts
    42

    Re: Receive "$" and "," in an InputBox

    Thanks for this!
    Reply With QuoteRegister To Reply
  8. 11-04-2015, 08:15 PM #8
    stnkynts
    stnkynts is offline
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Receive "$" and "," in an InputBox 

    I get a mismatch message.
    I don't. Curious
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. create text string: "NOT "[b2value]", NOT "[b3value]", ..., NOT "[b600value]"
    By savekels in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  2. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank
    By DanBraden in forum Excel General
    Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  5. Formula returning result of "True" or "False" instead of "1" or "0"
    By AllenMead in forum Excel General
    Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. HELP on "left","right","find","len","substitute" functions
    By serene83 in forum Excel General
    Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  7. Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next
    By BCB in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 05-13-2006, 05:02 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1