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

--
Message posted from http://www.ExcelForum.com

Hi
not possible with custom formats in this case

-----Original Message-----
What I want to do is have numbers entered in cells that
represent
lengths in milimeters. I want to have a custom format
that displays the
numbers in either milimeters or inches depending on an
external switch.


---
Message posted from http://www.ExcelForum.com/

.

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
-----Original Message-----
What I want to do is have numbers entered in cells that
represent
lengths in milimeters. I want to have a custom format
that displays the
numbers in either milimeters or inches depending on an
external switch.


---
Message posted from http://www.ExcelForum.com/

.

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




In article ,
pH7 wrote:

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