VBA Form Textbox Color Format Determined by Parameter Worksheet

[Jonny117]

Hello Everyone, first post for me on here though I've visited this site from time to time so I'll start by saying thanks to all those helping to answer questions, give tips, etc.

Now to the issue I am having with my VBA Userform.

I have a mostly completed form for inspectors to fill in values and submit data once complete, I am trying to get the form text boxes to turn yellow based on the values selected in a "Size" and "SDR" combobox and then if the value they enter in a textbox is outside the acceptable tolerance on a parameters chart said textbox turn yellow (see parameter chart example below). I am unable to figure out how to color a userform textbox based on a value in my parameters sheet when the parameter value is determined by two columns. In the chart below you can see that multiple rows have the "size" column value of "2" "3" etc. and also the "SDR" column also comes into play when I am trying to format the color of a text box if the value entered in said textbox falls outside those values.

Example: If the inspector selects Size: 2 in one combobox and then SDR: 7 in another and then if the operator enters a Wall Thickness value less than 0.339 or greater than 0.380 then the textbox should turn yellow.
If the inspector selects Size:2 and SDR: 9 in the comboboxes then this time if the operator enters a Wall Thickness value less than 0.264 or greater than 0.296 then the textbox will turn yellow.

[TABLE="class: grid, width: 702"]
<tbody>[TR]
[TD]SIZE[/TD]
[TD]SDR[/TD]
[TD="colspan: 2"]WALL THICKNESS[/TD]
[TD="colspan: 2"]HUB THICKNESS[/TD]
[TD="colspan: 2"]PIPE O.D.[/TD]
[TD="colspan: 2"]FLANGE O.D.[/TD]
[TD="colspan: 2"]OAL[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]0.339[/TD]
[TD]0.380[/TD]
[TD]0.450[/TD]
[TD]0.504[/TD]
[TD]2.369[/TD]
[TD]2.381[/TD]
[TD]3.980[/TD]
[TD]4.000[/TD]
[TD]6.000[/TD]
[TD]6.250[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9[/TD]
[TD]0.264[/TD]
[TD]0.296[/TD]
[TD]0.450[/TD]
[TD]0.504[/TD]
[TD]2.369[/TD]
[TD]2.381[/TD]
[TD]3.980[/TD]
[TD]4.000[/TD]
[TD]6.000[/TD]
[TD]6.250[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11[/TD]
[TD]0.216[/TD]
[TD]0.242[/TD]
[TD]0.402[/TD]
[TD]0.450[/TD]
[TD]2.369[/TD]
[TD]2.381[/TD]
[TD]3.980[/TD]
[TD]4.000[/TD]
[TD]6.000[/TD]
[TD]6.250[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7[/TD]
[TD]0.500[/TD]
[TD]0.560[/TD]
[TD]0.625[/TD]
[TD]0.700[/TD]
[TD]3.492[/TD]
[TD]3.508[/TD]
[TD]4.988[/TD]
[TD]5.012[/TD]
[TD]6.000[/TD]
[TD]6.250[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]0.389[/TD]
[TD]0.436[/TD]
[TD]0.625[/TD]
[TD]0.700[/TD]
[TD]3.492[/TD]
[TD]3.508[/TD]
[TD]4.988[/TD]
[TD]5.012[/TD]
[TD]6.000[/TD]
[TD]6.250[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD]0.318[/TD]
[TD]0.356[/TD]
[TD]0.598[/TD]
[TD]0.670[/TD]
[TD]3.492[/TD]
[TD]3.508[/TD]
[TD]4.988[/TD]
[TD]5.012[/TD]
[TD]6.000[/TD]
[TD]6.250[/TD]
[/TR]
</tbody>[/TABLE]


Below is working code I have for the parameters that are just determined by Size, because for instance the Pipe O.D. parameter is only determined by size and not also the SDR as seen in the above chart.

Private Sub PipeODText_AfterUpdate()

 Dim Found As Range
    On Error Resume Next
   
    If PipeODText.Value <> "" Then
        PipeODText.BackColor = rgbWhite
        PipeODLabel.ForeColor = Me.ForeColor
    End If
    
    If SizeCombo.Value <= 63 Then
      ' Find size in table
      Set Found = wsParameters.Range("A:A").Find(what:=SizeCombo.Value, lookat:=xlWhole, LookIn:=xlValues)
      If Found Is Nothing Then
         MsgBox "Could not find size value " & SizeCombo.Value & " in Parameters."
      Else
         If CDbl(PipeODText.Value) < wsParameters.Cells(Found.Row, "H") Or _
            CDbl(PipeODText.Value) > wsParameters.Cells(Found.Row, "I") Then
            PipeODText.BackColor = rgbYellow
         Else
            PipeODText.BackColor = rgbWhite
         End If
      End If
   End If


End Sub

This is the troubling code, I tried to get similar code below to work for the values determined by Size and SDR but haven't had any success.

Private Sub WT1Text_AfterUpdate()

Dim Found As Range
    On Error Resume Next
   
    If WT1Text.Value <> "" Then
        WT1Text.BackColor = rgbWhite
        WT1Label.ForeColor = Me.ForeColor
    End If
    
    If SizeCombo.Value <= 63 Then
     If SDRCombo.Value <= 32.5 Then
      ' Find size in table
      Set Found = wsParameters.Range("A:A").Find(what:=SizeCombo.Value, lookat:=xlWhole, LookIn:=xlValues)
      ' Find sdr in table
      Set Found = wsParameters.Range("C:C").Find(what:=SDRCombo.Value, lookat:=xlWhole, LookIn:=xlValues)
      If Found Is Nothing Then
         MsgBox "Could not find SDR value " & SDRCombo.Value & " in Parameters."
        
      Else
         If CDbl(WT1Text.Value) < wsParameters.Cells(Found.Row, "D") Or _
            CDbl(WT1Text.Value) > wsParameters.Cells(Found.Row, "E") Then
            WT1Text.BackColor = rgbYellow
         Else
            WT1Text.BackColor = rgbWhite
         End If
        End If
       End If
      End If
    
End Sub

Is there a way to provide my project for better review?

Hopefully someone can help with this.

Regards,
Jon

 

[Jonny117]

Anyone have any ideas on this?

I'm new to VBA coding and am learning as I go.

I've done some searching around but having a hard time finding something that will work for my case here.

Regards,
Jon

 

[Jonny117]

I may have figured it out with the below:

Private Sub WT1Text_AfterUpdate()

    Dim Found As Range
    On Error Resume Next
    
    ' Find size in parameters sheet
    With SizeCombo.Value <= 63
        Set Found = wsParameters.Range("A:A").Find(what:=SizeCombo.Value, lookat:=xlWhole, LookIn:=xlValues)
    End With
    
    ' Find sdr in parameters sheet
    With SDRCombo.Value <= 32.5
        Set Found = wsParameters.Range("C:C").Find(what:=SDRCombo.Value, lookat:=xlWhole, LookIn:=xlValues)
    End With
    
    ' Compare value entered in textbox to parameters sheet
    If CDbl(WT1Text.Value) < wsParameters.Cells(Found.Row, "D") Or _
        CDbl(WT1Text.Value) > wsParameters.Cells(Found.Row, "E") Then
            WT1Text.BackColor = rgbYellow
        Else
            WT1Text.BackColor = rgbWhite
    End If
        
    ' if textbox left blank, turn white
    If WT1Text.Value = "" Then
        WT1Text.BackColor = rgbWhite
    End If
    
End Sub

 

[codeliftsleep]

Have you tried conditional formatting? No code would be needed in this instance

 

[Jonny117]

Have you tried conditional formatting? No code would be needed in this instance

Hi Codeliftsleep

What I'm trying to do with this userform is have inspectors use the form to submit the data and not have to deal with the spreadsheet at all, so when they open the excel file it automatically opens the userform in fullscreen and when they close the userform it saves and closes the entire file but I still want to give them an easy visual clue on the userform itself if something is out of spec with the values they entered.

It looks like the code I posted in my previous post kinda works for some sizes but not all, truly confused on why this would be.