+ Reply to Thread
Results 1 to 5 of 5

Formatting percentages in a userform

  1. #1
    Registered User
    Join Date
    04-01-2016
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    56

    Formatting percentages in a userform

    I'm sorry if this problem is not very clear, or if it has a very simple solution that I am too simple to see.

    I'm new to vba, and I have butchered a couple of blocks of code from some very kind people in order to make a userform. 4 of the text boxes on the form are for users to input percentages, which are then used in a calculation on the spreadsheet, the spreadsheet then adds some info to the userform, which the user then clicks a button to submit all the data back to the 'database' on the spreadsheet.

    I have got it to work ok, so that there are spin buttons linked to the text boxes, and it won't let you add the results if it's not a number between 0-100 entered in the text box. The problem is that when it lands on the worksheet, if the user has inputted '1', it goes to the worksheet as '100%' rather than '1%' (I guess because excel treats percentages as fractions). I really don't want the user to have to input '0.10' for 10% etc. I have tried to change the cell on the worksheet to 'percentage', but the userform seems to overwrite the formatting. I have the same problem with a currency amount actually, it seems to work okay, except excel tells me it is a number stored as text.

    Is there a way to format the numbers/text boxes so that inputting '1' in the text box really does mean '1%'?

    Any help greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Formatting percentages in a userform

    When writing your value from the text box to the workbook

    Divide by 100.

    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    04-01-2016
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Formatting percentages in a userform

    Thank you very much for the response. That gave me a type mismatch error, I think because I have restricted the values already. If I tried to do it 'after the fact' it gave me a correct calculation, but then returned a strange value to the database (might have been dividing by 100 twice, or something).

    Really what I would like to do, that is by far the least frightening thing for a VBA-naif like me, is just format those text boxes in exactly the same way as one formats a cell on the worksheet to 'percentage'. I'm pretty sure that there is a command for this because I have seen it on the intellisense list, but I don't know the correct syntax to use it, or if I need to create a new subroutine or what.

    Edit: and I actually need to know how to do this for another bit too I think, because there is a currency value that, although it is formatted correctly (£#,###.##), when it reaches the database, excel is telling me it's a number stored as text, so I really need to format those to be 'currency' too. Any help greatly appreciated, thank you very much.
    Last edited by bevc; 04-10-2016 at 04:59 AM. Reason: added detail

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Formatting percentages in a userform

    Please Login or Register  to view this content.

    Or Maybe

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-01-2016
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Formatting percentages in a userform

    It looks like that's worked, thank you so much!

+ 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. [SOLVED] Conditional Formatting with Percentages
    By JakeMann in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2014, 09:01 AM
  2. conditional formatting and percentages
    By fentiger79 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2012, 04:25 AM
  3. [SOLVED] Conditional formatting for percentages
    By dr_jon in forum Excel General
    Replies: 1
    Last Post: 08-08-2012, 11:31 AM
  4. Conditional Formatting with Percentages
    By este994 in forum Excel General
    Replies: 12
    Last Post: 04-27-2012, 02:57 PM
  5. Conditional Formatting with percentages
    By tracykay1 in forum Excel General
    Replies: 5
    Last Post: 12-12-2011, 02:17 PM
  6. formatting percentages
    By Marcus Hartley in forum Excel General
    Replies: 6
    Last Post: 06-04-2007, 01:00 AM
  7. Percentages and formatting cells
    By Louise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2006, 07:45 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