+ Reply to Thread
Results 1 to 4 of 4

Change event to Convert (calculate) user input if user types % sign

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Change event to Convert (calculate) user input if user types % sign

    How can I convert a user's input from the input value to something else IF the user typed a value and % sign? In other words, user types 10% in A1, which i capture in the change event. Since user typed % sign, I want to change the original input (10%) to the result of 10% * A2. The problem is for this to work the cell numberFormat MUST be accounting and two decimals. I want the cell format to be 0 instead of 0.00 though.

    Below is what I have that works only if the format per below (accounting). It basically takes the user's input and recalculates it as a percent of the amount in the adjacent column..if the user types 10%. Keep in mind the target numberFormat must remain as 0 (and not 0% nor 0.00, which is what the below code must do to work). Any tips are greatly appreciated!

    To rephrase:
    1. Target must be formatted as a number, eg., 0.
    2. If user inputs target value with a % (eg, 10%), recalculate input as 10% * adjacent column. Retain original format as 0.
    3. If user inputs a number target value (eg 10), no action to take. Retain format as 0.

    Please Login or Register  to view this content.
    Last edited by soundneedle; 04-18-2016 at 10:57 AM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,991

    Re: Change event to Convert (calculate) user input if user types % sign

    Hi Soundneedle...

    Fistly please read the forum rules, I refer particularly to rule 3 regarding using code tags when posting code.

    Now your example description starts by talking about A1 and A2, and A1 changes getting multiplied by A2, yet your offset suggests you are not moving forward but backward. If you change A1, then an offset of (0,-1) is impossible since there is no column before A.
    Additionally you later mention the "adjacent column" yet your example (A1/A2) is looking at the next row, not column.

    Can you provide a clearer explanation?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    07-06-2011
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    4
    Quote Originally Posted by Arkadi View Post
    Hi Soundneedle...

    Fistly please read the forum rules, I refer particularly to rule 3 regarding using code tags when posting code.

    Now your example description starts by talking about A1 and A2, and A1 changes getting multiplied by A2, yet your offset suggests you are not moving forward but backward. If you change A1, then an offset of (0,-1) is impossible since there is no column before A.
    Additionally you later mention the "adjacent column" yet your example (A1/A2) is looking at the next row, not column.

    Can you provide a clearer explanation?

    Basically, the point is this code only works if the target is specifically formatted as "accounting". My question is why... What's so special about this format? I've tried similar number formats where it has two decimals, for example. However, when user types 20% into the target excel immediately converts to 0.2. If the format is accounting excel keeps the % character which allows my code to work properly.

    The code is dumbed down for simplicity. Takes the target value and determines if user typed the number input as a percentage. If so, do a simple calc using target and adjacent cell. No issues with the calc part... Just the initial part where it determines if a percent (sign) was in the user input.
    Last edited by soundneedle; 04-18-2016 at 10:56 AM.

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    4,991

    Re: Change event to Convert (calculate) user input if user types % sign

    What if you just format the cells as text? you can still make the string you later put back in r1 a string that is the numerical value you want, formatted the way you want it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 08-04-2015, 12:09 PM
  2. Need an event when user types in a cell?
    By RustyNail in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2015, 03:41 PM
  3. Change output after list selection and force user to sign name
    By switzd0d in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-03-2015, 04:48 PM
  4. [SOLVED] Use Change Event only if the value of the drop down is changed by the user
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2013, 11:27 AM
  5. change event macro to evaluate windows user who initiated the change then send email
    By pmanoloff in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2012, 03:31 PM
  6. How to trigger a macro on a worksheet on the event of user-input?
    By fiven in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2005, 11:38 PM
  7. How to auto-trigger a macro on a worksheet on the event of user-input?
    By fiven in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-19-2005, 11:23 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1