+ Reply to Thread
Results 1 to 17 of 17

Textbox to enter value in either format

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Textbox to enter value in either format

    On the attached example i have a user form (click Add New Hedge button) and on this form is a textbox for 'Avg. Price'. The value of entered from this box will go into the next available row in column on 'Unsettled Hedges' worksheet.

    My problem is sometimes i would to enter the price as a fraction, e.g. 1/2 and other i would like to enter the price as a decimal, e.g. 1.5. At the moment if i enter the price as a decimal figure this will convert it to a fraction in column D of the 'unsettled hedges' worksheet.

    What do i need to change in my code to make it enter the price in either format?

    Thanks,
    Adam.
    Attached Files Attached Files
    Last edited by adam2308; 11-30-2009 at 09:17 AM.

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Hi,
    The format solution is the first step ...
    Now, if you input price as a fraction, it is not modified ...
    If you want to refine, you will need an event macro worksheet_change to detect how input is made and adapt format accordingly ...

    HTH

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Textbox to enter value in either format

    If on the off chance that the fraction format is only to apply to values < 1 and for all other values you wish to use standard format then you could perhaps try applying a custom format to the destination cell of: [>=1]General;?/?

  4. #4
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    Thank you both for your replies.

    DonkeyOte... The fraction format could apply to any value at all.

    JeanRage... I think what you are suggesting could be along the right lines but i don't know how to do this. Any chance you (or anybody else) could give me an example, please?

    Many Thanks.

  5. #5
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Hi,

    See attached test file as a starting point ...

    HTH
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    I'm sorry JeanRage, i'm not sure what you want me to test.

    I tried typing 1.25 into D2 and was hoping it would remain in decimal format but it reverted to fractional format (?/???).

    Is there something else i should be doing?

  7. #7
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Sorry ... I misunderstood your requirement ...
    See attached attempt ...

    HTH
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    that's more like what i mean but when i tried to add the code into the unsettled hedges worksheet then my 'Add New Hedge' form did not enter the info.

    Is there anything i need to change or add?

    I have attached my example with the additional from your your workbook.
    Attached Files Attached Files
    Last edited by adam2308; 11-27-2009 at 04:00 PM.

  9. #9
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Hello Adam,

    First of all, Congratulations on the time and effort you have already invested in your project ...

    With no test data at all, it not is possible for me for detect potential mistakes ...
    see attached test file, and let me know if it is OK or not ...

    HTH
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    Hi Jeanrage,

    The code you have added to the worksheet 'unsettled hedges' works fine when manually entering data directly into column D of this worksheet. However, the idea is to enter the data through the 'Input Form' which can be accessed through the 'Add New Hedge' Button on the summary sheet.

    The problem is though, with the code added to 'unsettled hedges' worksheet, the 'enter' button on the input form no longer works. Do you understand why this no longer works?

    Thanks,
    Adam.

  11. #11
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Hi Adam,

    Have a go with this second attempt ...

    HTH
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    Hi Jeanrage,

    This seems to work fine on your example file but can you explain what you have changed as i need to know what to change in my original workbook.

    I noticed that you changed a line in the code of the 'Enter Button' of the 'Input Form' from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    .

    What i don't understand from the new line of code is how does it know which textbox value to enter into the next available row of column D?

    Thanks for your advice.

  13. #13
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Hi Adam,

    I have just added this line to ensure cell is formatted as text ...

    HTH

  14. #14
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    So how does it know which textbox value to enter into column D?

    When i altered this line of code in my original workbook there was nothing being entered into column D of the 'Unsettled Hedges' worksheet. I'm wondering am i missing something else?

  15. #15
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Quote Originally Posted by JeanRage View Post
    Hi Adam,

    I have just added this line to ensure cell is formatted as text ...

    HTH
    Added ... means that I have not removed the line
    .Cells(iRow, 4).Value = Me.TextBox3.Value

  16. #16
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Re: Textbox to enter value in either format

    Sorry Jeanrage, completely missed that!! This works fine in my original workbook now.

    I may also have another issue to post on the forum about the same workbook, in the near future... so keep your eye out!!

    Thank you very much for all your help! I shall now add some positive feedback to your reputation.

  17. #17
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Textbox to enter value in either format

    Glad you could fix your problem ...
    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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