Userform inconsistent responses

[Jnb99]

Hi everyone,

I'm busy making a userform to calculate the the voltage drop of electrical cables. Screenshot attached with the combo and textbox's for reference.

I am using the code below to format TextBox4 based on the answer. The formatting is self explanitory:

' Determine the max limit based on ComboBox2
        Select Case Me.ComboBox2.Value
            Case "2 Core Single phase"
                maxLimit = 11.5
            Case "3/4 Core Three phase"
                maxLimit = 20
            Case Else
                maxLimit = 0
        End Select
        
        ' Check if the value exceeds the limit and change background color and text accordingly
        If valueTextBox4 > maxLimit And maxLimit > 0 Then
            Me.TextBox4.BackColor = RGB(255, 0, 0) ' Red color
            Me.TextBox4.Value = "Choose bigger Cable"
        Else
            Me.TextBox4.BackColor = RGB(255, 255, 255) ' White color
            Me.TextBox4.Value = valueTextBox4
        End If

The issue I am having is, that when "3/4 Core Three phase" is selected in ComboBox2, TextBox4 is not formatting even when the value exceeds 20 as per the parameter set in the code.

Any advise?

Full code below:

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Set ws = Sheets("table")
    
    Debug.Print "Initializing UserForm"
    
    ' Clear existing items
    Me.ComboBox1.Clear
    Me.ComboBox2.Clear
    Me.ComboBox3.Clear
    
    ' Populate ComboBox1 with values from F1 and G1
    If Not IsEmpty(ws.Range("F1").Value) Then
        Me.ComboBox1.AddItem ws.Range("F1").Value
        Debug.Print "ComboBox1 - F1: " & ws.Range("F1").Value
    End If
    If Not IsEmpty(ws.Range("G1").Value) Then
        Me.ComboBox1.AddItem ws.Range("G1").Value
        Debug.Print "ComboBox1 - G1: " & ws.Range("G1").Value
    End If
    
    ' Populate ComboBox2 with values from C2 and D2
    If Not IsEmpty(ws.Range("C2").Value) Then
        Me.ComboBox2.AddItem ws.Range("C2").Value
        Debug.Print "ComboBox2 - C2: " & ws.Range("C2").Value
    End If
    If Not IsEmpty(ws.Range("D2").Value) Then
        Me.ComboBox2.AddItem ws.Range("D2").Value
        Debug.Print "ComboBox2 - D2: " & ws.Range("D2").Value
    End If
    
    ' Populate ComboBox3 with values from A3 to A19
    Dim cell As Range
    For Each cell In ws.Range("A3:A19")
        If Not IsEmpty(cell.Value) Then
            Me.ComboBox3.AddItem cell.Value
            Debug.Print "ComboBox3 - A" & cell.Row & ": " & cell.Value
        End If
    Next cell
End Sub

Private Sub ComboBox1_Change()
    UpdateResult
End Sub

Private Sub ComboBox2_Change()
    UpdateResult
End Sub

Private Sub ComboBox3_Change()
    UpdateResult
End Sub

Private Sub UpdateResult()
    Dim ws As Worksheet
    Dim result As Variant
    Dim lookupValue As String
    Dim colIndex As Integer
    Dim tableRange As Range
    Dim foundMatch As Boolean
    Dim r As Long

    Set ws = Sheets("table")
    
    ' Check if all ComboBoxes have values selected
    If Me.ComboBox1.ListIndex = -1 Or Me.ComboBox2.ListIndex = -1 Or Me.ComboBox3.ListIndex = -1 Then
        Me.TextBox3.Value = "Select all values"
        Me.TextBox4.Value = ""
        Me.TextBox4.BackColor = RGB(255, 255, 255) ' Reset background color to white
        Exit Sub
    End If
    
    ' Determine the column index based on the cable type
    Select Case Me.ComboBox2.Value
        Case ws.Range("C2").Value
            colIndex = 3
        Case ws.Range("D2").Value
            colIndex = 4
        Case Else
            Me.TextBox3.Value = "Cable Type Not Found"
            Me.TextBox4.Value = ""
            Me.TextBox4.BackColor = RGB(255, 255, 255) ' Reset background color to white
            Exit Sub
    End Select
    
    ' Determine the table range based on cable material
    Select Case Me.ComboBox1.Value
        Case ws.Range("F1").Value
            Set tableRange = ws.Range("A3:D19")
        Case ws.Range("G1").Value
            Set tableRange = ws.Range("A22:D22")
        Case Else
            Me.TextBox3.Value = "Cable Material Not Found"
            Me.TextBox4.Value = ""
            Me.TextBox4.BackColor = RGB(255, 255, 255) ' Reset background color to white
            Exit Sub
    End Select
    
    ' Ensure lookupValue is treated as a string
    lookupValue = CStr(Me.ComboBox3.Value)
    
    ' Initialize foundMatch
    foundMatch = False

    ' Manually search for the lookupValue
    For r = 1 To tableRange.Rows.Count
        If CStr(tableRange.Cells(r, 1).Value) = lookupValue Then
            result = tableRange.Cells(r, colIndex).Value
            foundMatch = True
            Exit For
        End If
    Next r
    
    ' Handle the result
    If foundMatch Then
        Me.TextBox3.Value = result
    Else
        Me.TextBox3.Value = "Value Not Found"
    End If
    
    ' Debugging: Print the result to Immediate Window
    Debug.Print "Lookup Value: " & lookupValue
    Debug.Print "Table Range: " & tableRange.Address
    Debug.Print "Column Index: " & colIndex
    Debug.Print "Result: " & Me.TextBox3.Value
End Sub

Private Sub CommandButton1_Click()
    Dim maxLimit As Double
    Dim valueTextBox4 As Double

    ' Calculate result for TextBox4 when CommandButton1 is pressed
    If IsNumeric(Me.TextBox1.Value) And IsNumeric(Me.TextBox2.Value) And IsNumeric(Me.TextBox3.Value) Then
        Me.TextBox4.Value = (CDbl(Me.TextBox1.Value) * CDbl(Me.TextBox2.Value) * CDbl(Me.TextBox3.Value)) / 1000
        
        ' Convert TextBox4 value to a number for validation
        valueTextBox4 = CDbl(Me.TextBox4.Value)
        
        ' Determine the max limit based on ComboBox2
        Select Case Me.ComboBox2.Value
            Case "2 Core Single phase"
                maxLimit = 11.5
            Case "3/4 Core Three phase"
                maxLimit = 20
            Case Else
                maxLimit = 0
        End Select
        
        ' Check if the value exceeds the limit and change background color and text accordingly
        If valueTextBox4 > maxLimit And maxLimit > 0 Then
            Me.TextBox4.BackColor = RGB(255, 0, 0) ' Red color
            Me.TextBox4.Value = "Choose bigger Cable"
        Else
            Me.TextBox4.BackColor = RGB(255, 255, 255) ' White color
            Me.TextBox4.Value = valueTextBox4
        End If
    Else
        Me.TextBox4.Value = "Invalid Input"
        Me.TextBox4.BackColor = RGB(255, 255, 255) ' White color
    End If
    
    ' Debugging: Print the result to Immediate Window
    Debug.Print "TextBox1: " & Me.TextBox1.Value
    Debug.Print "TextBox2: " & Me.TextBox2.Value
    Debug.Print "TextBox3: " & Me.TextBox3.Value
    Debug.Print "TextBox4: " & Me.TextBox4.Value
    Debug.Print "TextBox4 Background Color: " & Me.TextBox4.BackColor
End Sub

Private Sub CommandButton2_Click()
    ' Clear all textboxes
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox4.BackColor = RGB(255, 255, 255) ' Reset background color to white
    
    ' Clear all comboboxes
    Me.ComboBox1.Clear
    Me.ComboBox2.Clear
    Me.ComboBox3.Clear
    
    ' Repopulate ComboBoxes
    Call UserForm_Initialize
    
    ' Debugging: Print a message to Immediate Window
    Debug.Print "All controls cleared and ComboBoxes repopulated"
End Sub

Thanks

JNB99

 

[Candyman8019]

Try TextBox4.value rather than valueTextBox4