Inch/Metric toggle with a button 2

[cogsman1]

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.

 

[TomBarsh]

I can see numerous solutions to this, using either cell formulas alone or VBA (the latter is probably easier). Or even separate worksheets for each set of units, then link the two sheets via their inputs.

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.

 

[cummings54]

I've provided a link to a sample workbook. It has a toggle button that will change values and unit symbols based on the state of the toggle button. Cells of interest have been identified using a range name. These include units values and units symbols. The example is simple showing just length units and symbols but could be applied to as many conversions as required.

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

 

[cogsman1]

Thanks cummings54, that is exactly what I am trying to accomplish. Where do I find instructions to learn how you did it? I see your macro but don't see how it knows to convert to cm. I want to apply it the way I need in my worksheet. Now I know it IS possible!

 

[cummings54]

The macro button is an ActiveX control. Use the menu item View:Toolbars:Control Toolbox and ensure that the Control Toolbox is checked. Click the View Code button on the tool bar which will open VBA editor showing the event procedure triggered by clicking the toggle control as follows:

Private Sub Converter_Click()
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

The toogle control was renamed Converter. the value property of the toggle control is true or false so it can be used in the IIf function to select the conversion factor or unit symbol.

 

[cogsman1]

Now I have edited your macro to change from inch-millimeter and changed the labels on the button but how do I associate it with the cells in my worksheet. I am trying to follow the "Help" instructions but it doesn't seem to tell me that.

 

[electricpete]

A named range was setup as as LengthValue.

(hightlight range, insert / name / define / lengthvalue)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Also a named range lengthunit was used.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

There are many ways to do it.

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.

 

[electricpete]

Take a look here.

http://home.comcast.net/~electricpete/eng-tips/AlternateInputUnits.xls

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.

 

[electricpete]

I had the idea that I was going to make it easier with no vba, but got caught up with trying to keep two sets of inputs updated which dragged me into vba I think.

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.

 

[electricpete]

I just noticed that when you open a spreadsheet in internet explorer, you don't seem to have access to the excel menus to view the vba code. Would require download. So, here is the code in sheet1

Option Explicit

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.

 

[electricpete]

I made a few changes in the spreadsheet posted at the above link. Most notably, I moved the following statement:
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.