+ Reply to Thread
Results 1 to 10 of 10

Help with a formatting problem - Textbox to sheet and back.

  1. #1
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Help with a formatting problem - Textbox to sheet and back.

    Hi Forum.
    This text below is in the attached workbook too..



    I have a formatting problem.
    But first...
    - In Danish we use a dot as the 1000 separator.
    - And a comma as the 2 digit decimal separator.
    - Looks like this = 1.000,50

    I want to type the amounts in a Userform.Textboxes.
    To make the amount calculate on the sheet B4, I force the 2 digit decimal comma to change into a dot in the textbox, when typing a comma.
    So far so good and I don't care if it shows a dot as the 1000 separator too, but if it can be fixed automatic to show the English comma and still is calculation in B4, it would be perfect.

    Now my Problem.
    When I close and open the Userform again
    - It will not show the 1,000 separator dot (or comma).
    - And it will only show 1 digit after the 2 diget decimal number, when I type a zero (0) as the last digit = When opening the Userform it shows 1000.5 instead of 1000,50.

    Open the Userform to see how it will show in the Textboxes.
    The codes is on the button in the VBA.

    The codes to change the comma to the dot is on each Textbox.
    Plus some other codes I tried to use, to see if they would work.



    I really hope someone can help me and put the codes into my VBA, to resend it to me.
    I always have these trouble with the formatting, when trying to make a Date or an amount/a number, because of the difference the English VBA and the Danish formatted Excel
    Attached Files Attached Files
    Last edited by gnaske; 03-29-2021 at 09:16 AM.

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Help with a formatting problem - Textbox to sheet and back.

    I'd leave it in your regional format and use CDbl when writing to the sheet:

    Please Login or Register  to view this content.
    for example. When loading from the sheet, you can use Format to format the number however you like, but there doesn't appear to be any code in your form to actually load from the sheet - should there be?
    Rory

  3. #3
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Help with a formatting problem - Textbox to sheet and back.

    Hmmmm.

    Thank you,
    But I actually don't understand what it is you want to do with this code or where to place it.


    Codes I have in Textbox1

    Sub TextBox1_Change.
    Line 1 - What I write in Textbox1 will be written in Cell A2.
    Line 2 - Will change the comma I type as the decimal separator to a dot, to be able to calculate the amount in Cell A4.
    If I don't change the decimal comma separator to a dot, it won't calculate the amount in Cell A4.

    Sub TextBox1_Exit.
    This is REM.
    But I can instead put the Line 2 code from above here, to change the comma I type to a dot, when I Exit TextBox1.
    And then I want to show the 1000 separator in TextBox1 too on Exit, if it is possible, without destroying the amount calculation in A4.
    Please Login or Register  to view this content.

    The Open Button on Sheets("Ark1") which will open the Userform1.

    Sub CommandButton1_Click
    This open Userform1
    If there is a value in Cells A1 or A2 or both, I want too show the value in TextBox1 and TextBox2, when opening the Userform.
    With the 1000 separator and the decimal separator.
    But without it destroy the calculation in Cell A4.

    And I want it to show two digit in the decimal cifre if I type a zero(0) as the last digit and not only one digit.
    Not xx.5 - But xx.50

    The codes I REM is just codes I tried, but they didn't worked for me.

    Please Login or Register  to view this content.

    Note:
    When you load the userform and show the value from the cells A1 and A2 in the Textboxes, it will probably have an influence on the calculation in Cell A4.
    This because you will load the value from the Cells in the TextBox_Change, which again will change the value in the cells.

    I really can't figure all this out.
    And I want to make simple codes too, because I will have a lot of Textboxes in each Userform.

    I'm trying to make a Userform with 4 Textboxes for each date in a months, calling it "Master".
    And then to Export and save it, too Import it again and rename it as each month in a year.

    Then I can reuse the Userform, only change a few things in each Userform codes after renaming it to a new month.
    I have each month on a new Tab in Excel - Jan - Feb - Mar - Apr - and so on.


    If it's possible for you, you can try to make the codes for me.
    And then upload the Workbook with your codes, for me to see how you did it.
    Last edited by gnaske; 03-31-2021 at 04:08 AM.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Help with a formatting problem - Textbox to sheet and back.

    Try this - it will allow you to enter/view the numbers in your own regional format.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Help with a formatting problem - Textbox to sheet and back.

    Actually.

    It seems to work.
    But Now I have a new problem I can't figure out.

    Are you still here ?

    Ib
    Denmark
    Last edited by gnaske; 03-31-2021 at 10:38 AM.

  6. #6
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Help with a formatting problem - Textbox to sheet and back.

    Actually.

    It seems to work.
    But Now I have a new problem I can't figure out.

    Are you still here ?

    Ib
    Denmark

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Help with a formatting problem - Textbox to sheet and back.

    If it's a different problem, it needs a different thread please.

  8. #8
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Help with a formatting problem - Textbox to sheet and back.

    No it's the same tread, but the code makes something strange in my workbook.

    I don't know how to show it to you.
    Maybe TeamViewer online in my laptop.
    And talking using FB Messenger.
    Last edited by gnaske; 03-31-2021 at 11:09 AM.

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Help with a formatting problem - Textbox to sheet and back.

    That's not how this forum works.

  10. #10
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2010 and 2019, but only 2019 to test if my sheets works!
    Posts
    149

    Re: Help with a formatting problem - Textbox to sheet and back.

    Okay.

    I use both Office 2010 and 2019.
    And it seems as the problem is only when I use Excel 2010.
    Not Excel 2019.

    I really can't explain it.

    But then I just need to continue editing using Excel 2019 and not using 2010.

    I'll mark this thread again as Solved.

    Thank you very much one more time

+ 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] problem copy data from userform to sheet by multiple combobox & textbox
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2020, 07:55 AM
  2. [SOLVED] How to return back to textbox after
    By maroni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-19-2017, 08:02 AM
  3. [SOLVED] Userform textbox date sorting problem in main sheet
    By amoxia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2014, 09:28 PM
  4. [SOLVED] Userform TextBox Problem... formatting code clears data instead of formatting
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2012, 11:00 AM
  5. Moving focus back to first textbox
    By LisaPatch in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-01-2011, 10:23 AM
  6. Textbox Date formatting problem
    By Capp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2006, 02:20 PM
  7. [SOLVED] Textbox Date formatting problem
    By Capp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2006, 01:55 PM

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