Thread: Instr Function

Hi,
This is a portion of a larger Save event for a userform:

On the userform the user chooses a combobox value.
If the user chooses, in this case, Sundried Tomatoes,
I would like to

1. determine if Textbox12 contains text
2. compare that text to a number of terms (jar, jarred, packed in oil)

If any of theses terms are present, to raise a MsgBox event informing the user what to do.
Then, once the OK button is clicked, run a Clear_MultiPageControls sub.

If the condition is false just move along with the rest of the code.

My code does not seem to either check for the terms or raise the MsgBox event.

How do I fix this?

Case 2 'page 3  DRIED FRUITS
            Cells(lRow, 2).Value = Me.ComboBox4.Value

                If Me.ComboBox4.Text = "Sundried Tomatoes" And _
                     InStr(TextBox12.Text, "jar jarred packed in oil") > 0 Then
                    
                    MsgBox "For Jarred, Oil Packed or any other prepared type of Sundried Tomato, please list under OTHER"
                    
                      Exit Sub
                         Clear_MultiPageControls
                    
                ElseIf Len(Me.TextBox12.Text) <> 0 Then
                   Cells(lRow, 2).Value = Me.ComboBox4.Value & ", " & TextBox12.Value
                
                If Len(Me.TextBox13.Text) <> 0 Then
                    Cells(lRow, 2).Value = Me.ComboBox4.Value & ", " & TextBox12.Value & " - " & TextBox13.Value
            
                    End If
                         End If

Thanks
Mark

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

InStr(TextBox12.Text, "jar jarred packed in oil") > 0

That expression will only return True if the entire phrase "jar jarred packed in oil" appears in the textbox.

This line will never be executed with the Exit Sub appearing immediately above it:

Clear_MultiPageControls

    If Me.ComboBox4.Text = "Sundried Tomatoes" And _
        (InStr(TextBox12.Text, "jar") Or _
        InStr(TextBox12.Text, "packed in oil") Or _
        InStr(TextBox12.Text, "barbecued")) Then

Thanks Shg,

That did the trick!

Here's the finished code:

        Case 2 'page 3  DRIED FRUITS
            Cells(lRow, 2).Value = Me.ComboBox4.Value
                
                If Me.ComboBox4.Text = "Sundried Tomatoes" And _
                     (InStr(TextBox12.Text, "jar") Or _
                        InStr(TextBox12.Text, "packed in oil") Or _
                            InStr(TextBox12.Text, "jarred")) Then
                
                MsgBox "For Jarred, Oil Packed or any other prepared type of Sundried Tomato, please list under OTHER"
                   Clear_MultiPageControls
                      Exit Sub
                         
                    
                ElseIf Len(Me.TextBox12.Text) <> 0 Then
                   Cells(lRow, 2).Value = Me.ComboBox4.Value & ", " & TextBox12.Value
                
                If Len(Me.TextBox13.Text) <> 0 Then
                    Cells(lRow, 2).Value = Me.ComboBox4.Value & ", " & TextBox12.Value & " - " & TextBox13.Value
            
                    End If
                         End If

Good job.

Testing for jarred is redundant if you've tested for jar.