Conditional formatting

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

Is there a way to suppress the display of the decimal value in a percentage
format if it is 0? The situation is that I don't want to display 99.96334%
as 100%, but neither do I want to display all of the 100% values as 100.0%.
Any ideas? Conditional formatting seems to apply only to colors and
bolding/italucs.

Lynn Trapp's suggestion in "Pulling my hair out" looks promising, but I
don't know how to specify the number of decimals in a percentage format. (I
don't know how to reference an individual column, either, but I'm thinking
maybe that's just a memory lapse....)

"Assuming the name of your checkbox on the form is cboApplies then you can
put this in the Format event of your reports Detail section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.cboApplies = -1 Then
Me.cboApplies.Visible = True
Else
Me.cboApplies.Visible = False
End If
End Sub"
 
Hi Laurel,

You can do just about anything in this line by writing a VBA function
that examines the number and returns a string containing it formatted as
you want. Often you can call the function directly in an expression in
the control's Value property; otherwise you can call it in an event
procedure.

Here's a little (air code) example.

Public Function CustomPercentFormat(V As Variant) As Variant

Const IGNORE As Double = 1E-9
Dim S As String

CustomPercentFormat = Null
If IsNull(V) Then Exit Function
If Not IsNumeric(V) Then Exit Function

If Abs(V - 1) <= IGNORE Then
'Treat V as exactly equal to 1
S = "100%"
Else
'Format with up to X decimal places
S = Format(V, "0.##########%")
'Alternatively format with fixed decimal places
'S = Format(V, "0.000000%)

End If
End Function
 
Thank you!!!

John Nurick said:
Hi Laurel,

You can do just about anything in this line by writing a VBA function
that examines the number and returns a string containing it formatted as
you want. Often you can call the function directly in an expression in
the control's Value property; otherwise you can call it in an event
procedure.

Here's a little (air code) example.

Public Function CustomPercentFormat(V As Variant) As Variant

Const IGNORE As Double = 1E-9
Dim S As String

CustomPercentFormat = Null
If IsNull(V) Then Exit Function
If Not IsNumeric(V) Then Exit Function

If Abs(V - 1) <= IGNORE Then
'Treat V as exactly equal to 1
S = "100%"
Else
'Format with up to X decimal places
S = Format(V, "0.##########%")
'Alternatively format with fixed decimal places
'S = Format(V, "0.000000%)

End If
End Function
Click to expand...
 
Back
Top