Varying Text Length

mrcost

Registered User.
Local time
Today, 14:19
Joined
Oct 28, 2008
Messages
11
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 :cool:
 
i think to do this you need a format something like "$###0.00" that keeps every character, but replaces unused characters with a blank

Now, the problem is that any proportional spaced font will fail in this (which is why you can never get msgbox values to line up) - you need to use a fixed space font (of which courier is one I think)
 
I tried that with Times New Roman font, which I can't change, and it didn't work. Thanks for the suggestion though.

-Mrcost
 
Pretty sure you should be able to change fonts and use a monospace font. It should be a property of textbox. Is that where you tried to change it?
 
I tried changing the format in the textbox properties to be $#####0.00, but the ###s dissappeared upon entering. The VBA code in the On Format Event Procedure that I showed above will end up overriding the format in the textbox properties. Putting the extra ###### in the VBA code didn't do anything either.

Any other suggestions would be welcome.

Thanks,

Mrcost :cool:
 

Users who are viewing this thread

Back
Top Bottom