# Dynamic Decimal Placement

#### Av8tordude

##### Well-known Member
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:

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Special-K99

##### Well-known Member
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:

#### Av8tordude

##### Well-known Member
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:
``[COLOR=#574123]CCur(v) / [/COLOR][COLOR=#ff0000][B]100[/B][/COLOR]``

Last edited:

#### Av8tordude

##### Well-known Member
Still seeking help for this problems. Thank you kindly

#### Akuini

##### Well-known Member
Untested, but maybe this:

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

#### Av8tordude

##### Well-known Member
Runtime error 6...Overflow

Output shows \$1,000,000,000,000,000.000

#### Akuini

##### Well-known Member
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?

#### Av8tordude

##### Well-known Member
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

#### Akuini

##### Well-known Member
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``````

#### Av8tordude

##### Well-known Member
Correct...this is the output I'm looking for. Now the challenge is to implement it into the original code

1,102,082
Messages
5,484,577
Members
407,455
Latest member
Mishi

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...