Inch/Metric toggle with a button
Inch/Metric toggle with a button
(OP)
I have alot of data that I must share with the engineers in Japan. I work in inch values and they always want to see things in metric. I am trying to create a "Button" that would allow either just by clicking on it. Has anybody done this? Willing to share? I am trying to do this now and thought I might save some time by asking.
Thanks in advance for any help. I am using Excel 2003.
Thanks in advance for any help. I am using Excel 2003.





RE: Inch/Metric toggle with a button
It seems to me that the easiest solution is to simply include both sets of units on the same spreadsheet, either include parallel sets of calculations or by converting the results.
I suppose the method depends in part on what sort of calculations you are doing and how extensive they are.
RE: Inch/Metric toggle with a button
Range names have a limit to the size of the refers to text. So for very large ranges you may need to split the range in to sub-ranges and combine sub-ranges in one overal range.
HTH
RE: Inch/Metric toggle with a button
RE: Inch/Metric toggle with a button
CODE
Dim cl As Range
Converter.Caption = IIf(Converter, "CGS", "US CUSTOMARY")
For Each cl In Me.Range("LengthValue")
cl.Value = cl.Value * IIf(Converter, 2.54, 0.393700787401575)
Next cl
For Each cl In Me.Range("LengthUnit")
cl.Value = IIf(Converter, "cm", "inches")
Next cl
End Sub
RE: Inch/Metric toggle with a button
RE: Inch/Metric toggle with a button
(hightlight range, insert / name / define / lengthvalue)
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Inch/Metric toggle with a button
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Inch/Metric toggle with a button
I will post a spreadsheet with an alternate approach - no vba - simple if(boolean, valuea, valueb). Got it almost done but I have to run for an appointmennt.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Inch/Metric toggle with a button
http://hom
There are separate columns for English and metric units. (don't beat me up on my terminology about metric vs cgs etc).
Pick whichever units you like and enter your inputs in appropriate location. Everything is updated in both unit columns, including the opposite unit-system inputs.
Works by macro based on worksheet change event. The macro decides which column you have modified and which iputs need to be udpated. I think it would be pretty easy to extend to other problems.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Inch/Metric toggle with a button
There may be simpler solutions out there but you need to define a little better what type of control and functionality is required.
For example, does the program need to allow input in both types of units, or only input in 1 type of units and display in two types of units (that would certainly make for easy implementation).
When you change the units, do you want the program to change the input values similar to as done by cummings, or will the user be expected to input new values. Latter option makes things easier.
etc.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Inch/Metric toggle with a button
CODE
Private Sub Worksheet_Change(ByVal Target As Range)
' Disable events so don't retrigger worksheet_change event until it is done
Application.EnableEvents = False
' Check that only one cell at a time is altered
If Target.Rows.Count <> 1 Or Target.Columns.Count <> 1 Then
MsgBox ("Error - you must change only one cell at a time. Data may be corrupted")
Exit Sub
End If
' Check whether the altered cell is within input range
If Not WithinInputRange(Target) Then Exit Sub
' If get past above statement, then we are within the input range
' Check if the altered cell is in the English units Column
If Target.Column = Me.Range("EnglishColumn").Column Then
If Target.Offset(0, 1).Value = "inch" Then
Target.Offset(0, 2).Value = Target.Value * 2.54
' Note this handles any item in input range with units of inch - both length and radius
End If
If Target.Offset(0, 1).Value = "lbm/inch^3" Then
Target.Offset(0, 2).Value = Target.Value * 27.7
End If
End If
' Check if the altered cell is in the Metric units Column
If Target.Column = Me.Range("MetricColumn").Column Then
If Target.Offset(0, 1).Value = "cm" Then
Target.Offset(0, -2).Value = Target.Value / 2.54
End If
If Target.Offset(0, 1).Value = "g/cm^3" Then
Target.Offset(0, -2).Value = Target.Value / 27.7
End If
End If
' Note the code has made assumptions about the relative location of columns in the offset arguments
' i.e. assume four columns left to right:
' english values, english units, metric values, metric units
Application.EnableEvents = True
End Sub
Private Function WithinInputRange(mycell As Range)
' Test whether mycell is within the range identified by named range "inputrange"
Dim testcell As Range
WithinInputRange = False
For Each testcell In Me.Range("inputrange")
If testcell.Address = mycell.Address Then WithinInputRange = True
Next testcell
End Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Inch/Metric toggle with a button
Application.EnableEvents = False
further down in the code after all the exit sub statements.
This reduces the possibility to disable events witout re-enabling them.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.