Can you implement a custom format with VB code?

P

pH7

What I want to do is have numbers entered in cells that represen
lengths in milimeters. I want to have a custom format that displays th
numbers in either milimeters or inches depending on an external switch
 
S

Sandy V

What's your external switch?

In this eg I've assumed you change a cell named "units"
and a defined range named "mmIn" of one or more cells for
your custom formats:

in the Worksheet module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Not Intersect(Target, Range("units")) Is Nothing Then
If Range("units") = "mm" Then
Range("mmIn").NumberFormat = "_-""mm""* #,##0"
Else
Range("mmIn").NumberFormat = "_-""inch""* #,##0.00"
End If
End If

End Sub

Regards,
Sandy
 
J

JE McGimpsey

You can certainly do this using VBA, though not as a format.

One way:

Select all the cells you want to convert and name them, say
"convert_cells". Assume the "external switch" is a validated cell (named
say, "switch") that contains either "mm" or "in":

Public Sub ToggleConversion()
Dim rArea As Range
Dim rCell As Range
Dim dFactor As Double
dFactor = 25.4 ^ (-1 - 2 * (Range("switch").Value = "mm"))
For Each rArea In Range("convert_cells").Areas
For Each rCell In rArea
rCell.Value = rCell.Value * dFactor
Next rCell
Next rArea
End Sub
 
Top