Hello,
I have VBA code that looks at the first line in my report and reformats the text to include a dollar sign.
I would now like to make the dollar sign to appear the same distance from the right of the cell, no matter how many digits the number has. I was trying to do a formula to determine the length of the cell, then create a format based on the result (using case select).
Here is what I have that creates the dollar sign. There is one field that is a % format that I want to ignore. It is inserted in the Detail section of the report:
Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
m_count = CurrentRecord
Select Case m_count
Case 1
SetDetailTextboxFormat "$ #,##0;$ (#,##0);$ 0"
Case Else
SetDetailTextboxFormat "#,##0;(#,##0);0"
End Select
End Sub
Private Sub SetDetailTextboxFormat(format As String)
Dim ctrl As Control
Dim LResult As Long
For Each ctrl In Me.Detail.Controls
If ctrl.ControlType = acTextBox And ctrl.format <> "Percent" Then ctrl.format = format
Next
End Sub
Thanks for any help and Merry Christmas.
Mrcost
I have VBA code that looks at the first line in my report and reformats the text to include a dollar sign.
I would now like to make the dollar sign to appear the same distance from the right of the cell, no matter how many digits the number has. I was trying to do a formula to determine the length of the cell, then create a format based on the result (using case select).
Here is what I have that creates the dollar sign. There is one field that is a % format that I want to ignore. It is inserted in the Detail section of the report:
Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
m_count = CurrentRecord
Select Case m_count
Case 1
SetDetailTextboxFormat "$ #,##0;$ (#,##0);$ 0"
Case Else
SetDetailTextboxFormat "#,##0;(#,##0);0"
End Select
End Sub
Private Sub SetDetailTextboxFormat(format As String)
Dim ctrl As Control
Dim LResult As Long
For Each ctrl In Me.Detail.Controls
If ctrl.ControlType = acTextBox And ctrl.format <> "Percent" Then ctrl.format = format
Next
End Sub
Thanks for any help and Merry Christmas.
Mrcost
