Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Dynamic Decimal Placement

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Posts
    737
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Decimal Placement

    I using this code that formats the number as I type. I would like to make more dynamic to be able to choose the format base on a variable enter in another textbox. Currently the format is hard coded to two zeros after the decimal. I would like to enter (ex. 3) and the format would show $0.000

    Any suggestions?

    Thanks


    Code:
    Dim v As String
    
    
    Select Case Len(tbOPrice)
        Case 1
            tbOPrice = Format(tbOPrice, "$0\.00")
        Case Is > 1
            v = Replace(tbOPrice, "$", "")
            v = Replace(v, ".", "")
            tbOPrice = Format(CCur(v) / 100, "$#,#0.00")
        Case Else
    End Select
    Last edited by Av8tordude; Aug 15th, 2019 at 11:57 AM.
    The trick with sneaky users is not teaching them anything lest they get wise!

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,187
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamic Decimal Placement

    Am no VBA expert, does this work?
    You want something along the lines of

    Code:
    tbOPrice = Format(CCur(v) / 100, "$#,#0." & Right(10 ^ txtbox1,Len(10 ^ txtbox1)-1))
    10^txtbox1 : If txtbox1 = 2, result is 100, if txtbox1 is 3 result is 1000
    Performing a Right of that calculation using 1 less than the Length of it would knock the first digit off leaving 00 or 000 (I hope)
    You can then concatenate that to your decimal "$#,#0."
    Last edited by Special-K99; Aug 15th, 2019 at 12:26 PM.

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    737
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Decimal Placement

    How do I adjust the codes in red. I have to manually change the numbers in red from 2 zeros to 3 zeros so that it does not cause an error.

    Code:
    CCur(v) / 100
    Last edited by Av8tordude; Aug 15th, 2019 at 12:55 PM.
    The trick with sneaky users is not teaching them anything lest they get wise!

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    737
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Decimal Placement

    Still seeking help for this problems. Thank you kindly
    The trick with sneaky users is not teaching them anything lest they get wise!

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,056
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Dynamic Decimal Placement

    Untested, but maybe this:

    Code:
    z = WorksheetFunction.Rept(0, textbox1.Value)
    tbOPrice = Format(CCur(V) / 1 & z, "$#,#0." & z)

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Posts
    737
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Decimal Placement

    Runtime error 6...Overflow

    Output shows $1,000,000,000,000,000.000
    The trick with sneaky users is not teaching them anything lest they get wise!

  7. #7
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,056
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Dynamic Decimal Placement

    Hm, let's try this first:

    Code:
    Sub tryX()
    
    z = WorksheetFunction.Rept(0, 3)
    Debug.Print z
    Debug.Print 1 & z
    Debug.Print "$#,#0." & z
    
    End Sub
    The result in immediate window is:
    000
    1000
    $#,#0.000

    Isn't that what you expect?

  8. #8
    Board Regular
    Join Date
    Oct 2007
    Posts
    737
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Decimal Placement

    when I type in a value (ex 1),

    If the textbox variable is 2, the output should be $1.00
    If the textbox variable is 3, the output should be $1.000
    The trick with sneaky users is not teaching them anything lest they get wise!

  9. #9
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,056
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Dynamic Decimal Placement

    If I understand you correctly, maybe this:
    Code:
    Sub tryA()
    x = 1 ' in tbOPrice
    y = 3  'in the variable textbox
    
            V = Replace(x, "$", "")
            V = Replace(V, ".", "")
            z = WorksheetFunction.Rept(0, y)
            x = Format(x, "$#,#0." & z)
    
    Debug.Print x
    End Sub

  10. #10
    Board Regular
    Join Date
    Oct 2007
    Posts
    737
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Decimal Placement

    Correct...this is the output I'm looking for. Now the challenge is to implement it into the original code
    The trick with sneaky users is not teaching them anything lest they get wise!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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