# Thread: Dynamic Decimal Placement Thanks:  2 Post #5326321 (1)Post #5326626 (1) Likes:  1 Post #5326626 (1)

1. ## 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```  Reply With Quote

2. ## 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."  Reply With Quote

3. ## 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`  Reply With Quote

4. ## Re: Dynamic Decimal Placement

Still seeking help for this problems. Thank you kindly  Reply With Quote

5. ## Re: Dynamic Decimal Placement

Untested, but maybe this:

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

6. ## Re: Dynamic Decimal Placement

Runtime error 6...Overflow

Output shows \$1,000,000,000,000,000.000  Reply With Quote

7. ## 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?  Reply With Quote

8. ## 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  Reply With Quote

9. ## 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```  Reply With Quote

10. ## Re: Dynamic Decimal Placement

Correct...this is the output I'm looking for. Now the challenge is to implement it into the original code  Reply With Quote

## User Tag List

#### Tags for this Thread

case, dynamic, enter, format, select #### Posting Permissions

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