VBA if statement not working correctly

Question

I am trying to make a simple Temp converter to be able to correctly switch between Fahrenheit, Celsius, and Kelvin. The code isn't working the way it's supposed to. When I go from Fahrenheit to Kelvin it tells me it's using code to go from Fahrenheit to Celsius not Kelvin. I even added msgbox to tell me what is pulling what. I have also added the options for my combo boxes. It likes to ignore everything when using " " as a string. I have tried using both & and And to get this to work.

Private Sub CommandButton10_Click()
    If ComboBox1.Value = Fahrenheit & ComboBox2.Value = Celsius Then    
        Call FtoC        
    ElseIf ComboBox1.Value = Celsius & ComboBox2.Value = Fahrenheit Then
        Call CtoF        
    ElseIf ComboBox1.Value = Celsius & ComboBox2.Value = Kelvin Then    
        Call CtoK        
    ElseIf ComboBox1.Value = Kelvin & ComboBox2.Value = Celsius Then        
        Call KtoC        
    ElseIf ComboBox1.Value = Fahrenheit & ComboBox2.Value = Kelvin Then    
        Call FtoK    
    ElseIf ComboBox1.Value = Kelvin & ComboBox2.Value = Fahrenheit Then
        Call KtoF
    End If
End Sub   

This is the code for initialize for my comboboxes

Private Sub UserForm_Initialize()
    Call CallSetting

    'change position
    Me.StartUpPosition = 0
    Me.Top = (Application.Height / 2) - Me.Height / 2
    Me.Left = (Application.Width / 2) - Me.Width / 2

    With ComboBox1
        .AddItem ("Fahrenheit")
        .AddItem ("Celsius")
        .AddItem ("Kelvin")
    End With

    With ComboBox2
        .AddItem ("Fahrenheit")
        .AddItem ("Celsius")
        .AddItem ("Kelvin")
    End With

    TextBox2.Locked = True
End Sub

My macros are defined as the following:

Sub FtoC()
    TempConverter.TextBox2.Value = (TempConverter.Temp1.Value - 32) * (5 / 9)  
    MsgBox "I am FtoC"
End Sub

Sub CtoF()
    TempConverter.TextBox2.Value = (TempConverter.Temp1.Value * 0.55555) + 32
    MsgBox "I am CtoF"
End Sub

Sub CtoK()
    TempConverter.TextBox2.Value = TempConverter.Temp1.Value + 273  
    MsgBox "I am CtoK"
End Sub

Sub KtoC()
    TempConverter.TextBox2.Value = TempConverter.Temp1.Value - 273   
    MsgBox "I am KtoC"
End Sub

Sub FtoK()
    TempConverter.TextBox2.Value = ((TempConverter.Temp1.Value - 32) * (5 / 9)) + 273
    MsgBox "I am FtoK"
End Sub

Sub KtoF()
    TempConverter.TextBox2.Value = ((TempConverter.Temp1.Value - 273) * (5 / 9)) + 32    
    MsgBox "I am KtoF"
End Sub

Answer 1

Based on your replies, I assume that you did not have Option Explicit included in your module.

As such, your terms Farenheit, Celcius and Kelvin where implicitly defined and would have taken a default value (e.g. "" or 0). So your comparisons were not comparing what you thought they were.

When you put the "" around the words, you explicitly defined them as constant strings. So now your code could cleanly compare what was in the ComboBox with your options.

To cover off some of my comments, you can address your calculations in a single function. Here is an example:

Function ConvertTemperatureUnit(TemperatureValue as Double, FromUnits as String, ToUnits as String) as Double
    Select Case FromUnits
        Case "Fahrenheit"
            Select Case ToUnits
                Case "Celcius"
                    ConvertTemperatureUnit = (TemperatureValue -32) * (5/9)
                Case "Kelvin"
                    ConvertTemperatureUnit = ((TemperatureValue - 32) * (5 / 9)) + 273.15 ' accuracy!
                Case Else
                    ConvertTemperatureUnit = TemperatureValue ' covers an error case that was not covered in your previous code!
        Case "Celcius"
            Select Case ToUnits
                Case "Fahrenheit"
                    ConvertTemperatureUnit = (TemperatureValue * 5/9) + 32 ' keep the form of formula consist - just a readability thing.
                Case "Kelvin"
                    ConvertTemperatureUnit = TemperatureValue + 273.15
                Case Else
                    ConvertTemperatureUnit = TemperatureValue
        Case "Kelvin"
            Select Case ToUnits
                Case "Fahrenheit"
                    ConvertTemperatureUnit = ((TemperatureValue - 273.15) * (5 / 9)) + 32
                Case "Celcius"
                    ConvertTemperatureUnit = TemperatureValue - 273.15
                Case Else
                    ConvertTemperatureUnit = TemperatureValue
        Case Else
            ConvertTemperatureUnit = TemperatureValue ' soft fail.
    End Select

End Function

Thanks to @Peh, here is another example:

Function ConvertTemperatureUnit(TemperatureValue as Double, FromUnits as String, ToUnits as String) as Double
    Select Case FromUnits & "To" & ToUnits  ' the added "To" makes it human readable
        Case "FahrenheitToCelcius"
            ConvertTemperatureUnit = (TemperatureValue -32) * (5/9)
        Case "FahrenheitToKelvin"
            ConvertTemperatureUnit = ((TemperatureValue - 32) * (5 / 9)) + 273.15
        Case "CelciusToFahrenheit"
            ConvertTemperatureUnit = (TemperatureValue * 5/9) + 32 
        Case "CelciusToKelvin"
            ConvertTemperatureUnit = TemperatureValue + 273.15
        Case "KelvinToCelcius"
            ConvertTemperatureUnit = TemperatureValue - 273.15
        Case "KelvinToFahrenheit"
            ConvertTemperatureUnit = ((TemperatureValue - 273.15) * (5 / 9)) + 32
        Case Else
            ConvertTemperatureUnit = TemperatureValue ' soft fail.
    End Select

End Function

Answer 2

I have tried to add " " around the different temperature scales many times and nothing was working. I am not sure why, but it worked this time. I am still very confused, but it works now. Thank you for all your help.

    If ListFromUnit.Value = "Fahrenheit" And ListToUnit.Value = "Celsius" Then
        Call FtoC
    ElseIf ListFromUnit.Value = "Celsius" And ListToUnit.Value = "Fahrenheit" Then
         Call CtoF
    ElseIf ListFromUnit.Value = "Celsius" And ListToUnit.Value = "Kelvin" Then
         Call CtoK
    ElseIf ListFromUnit.Value = "Kelvin" And ListToUnit.Value = "Celsius" Then
         Call KtoC
    ElseIf ListFromUnit.Value = "Fahrenheit" And ListToUnit.Value = "Kelvin" Then
         Call FtoK
    ElseIf ListFromUnit.Value = "Kelvin" And ListToUnit.Value = "Fahrenheit" Then
         Call KtoF
    End If

Answer 3

you could avoid all that If Then ElseIf ... using CallByName method

Private Sub CommandButton10_Click()
    CallByName Me, ComboBox1.Value & "To" & ComboBox2.Value, VbMethod
End Sub

just name properly (and more meaningfully...) your routines:

Sub FahrenheitToCelsius()
    TempConverter.TextBox2.Value = (TempConverter.Temp1.Value - 32) * (5 / 9)
    MsgBox "I am FtoC"
End Sub

Sub CelsiusToFahrenheit()
    TempConverter.TextBox2.Value = (TempConverter.Temp1.Value * 0.55555) + 32
    MsgBox "I am CtoF"
End Sub

Sub CelsiusToKelvin()
    TempConverter.TextBox2.Value = TempConverter.Temp1.Value + 273
    MsgBox "I am CtoK"
End Sub

Sub KelvinToCelsius()
    TempConverter.TextBox2.Value = TempConverter.Temp1.Value - 273
    MsgBox "I am KtoC"
End Sub

Sub FahrenheitToKelvin()
    TempConverter.TextBox2.Value = ((TempConverter.Temp1.Value - 32) * (5 / 9)) + 273
    MsgBox "I am FtoK"
End Sub

Sub KelvinToFarenheit()
    TempConverter.TextBox2.Value = ((TempConverter.Temp1.Value - 273) * (5 / 9)) + 32
    MsgBox "I am KtoF"
End Sub