Unit Conversion

**j.mayo11** · 05-31-2010, 06:22 AM

hi all,

Im trying to create a form which has input for more than one unit type but which converts any inputs back to a single unit. I currently have a textbox and a combobox. the combobox has the unit types in it and i want the code to recognise which unit is selected and convert the value in the textbox accordingly before changing the combobox to show the units it converted to.
ie. pressure: bar, psi, pascal. If the user enters 20 in the textbox and psi in the combobox, the textbox would change to 1.38 and combobox to bar.

Is this possible

**shg** · 05-31-2010, 02:02 PM

You can create a conversion table someplace that lists units across the top, units down the left, and conversion factors in the body:

      ----A---- ----B---- ----C---- ----D----
  1   To \ From    bar       psi     pascal  
  2      bar    1.000E+00 6.897E-02 1.000E-05
  3      psi    1.450E+01 1.000E+00 1.450E-04
  4    pascal   1.000E+05 6.897E+03 1.000E+00

Then the conversion factor convert from one unit to the other is, for example,

=INDEX($A$1:$D$4, MATCH("pascal", $A$1:$A$4, 0), MATCH("bar", $A$1:$D$1, 0) )

**mikerickson** · 05-31-2010, 03:21 PM

Try putting this in the userform's code module

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .AddItem "bar"
        .AddItem "psi"
        .AddItem "pascal"
        Rem conversion units from .listindex to bar
        .List(0, 1) = 1
        .List(1, 1) = 14.5
        .List(2, 1) = 100000#
        .ListIndex = 0
    End With
    TextBox1.Text = vbNullString
End Sub

Private Sub ComboBox1_Click()
    With ComboBox1
        TextBox1.Text = CStr(Val(TextBox1.Tag) * Val(.List(.ListIndex, 1)))
    End With
End Sub

Private Sub TextBox1_AfterUpdate()
    Dim Amount As Double
    Dim AmountInBars As Double
    Amount = Val(TextBox1.Text)
    With ComboBox1
        AmountInBars = Amount * .List(.ListIndex, 1)
    End With
    TextBox1.Tag = AmountInBars
End Sub