Cross check data in 4 combo boxes with 4 columns on worksheet for a match (or not) and then give two outcomes

ezeepz

New Member
Joined
Jul 24, 2015
Messages
1
Hi,

Im new here (as a threader). First post. I have used your site many many times and have usually managed to find what I need. This one has got me though.

I have a userform. It has two option buttons (add or edit) and four comboboxes. To make the boxes visible I have to select an option first.

The issue I have relates to the ADD option button being selected. When selected, I can then select data from each combobox list until all have data in them all (data comes from other sheets). I have code which is activated by the 4th combobox change() event which should look for an exact match on my worksheet (ws1) in 4 columns (A,B,C & D). If it finds a match I get a message advising I have already got this product listed (exit sub), if no match I want it to open a 5th Textbox so I can add a price and then save to the ws1 datasheet. The code for the save part is already working fine.

Here is where I am at so far, There is code above this for the EDIT option select. I have tried adding an elseif, exit sub early and even multiple msgboxes to see where code is reading to try and understand what it is doing.

Any help would be appreciated.

Code:
If L1OptionButton1.Value = True Then    For iRow1 = 3 To ws1.Range("a65536").End(xlUp).Row
        If ws1.Cells(iRow1, 1).Value = Me.LockComboBox1.Text And _
            ws1.Cells(iRow1, 2).Value = Me.Lock2ComboBox2.Text And _
            ws1.Cells(iRow1, 3).Value = Me.Lock3ComboBox3.Text And _
            ws1.Cells(iRow1, 4).Value = Me.Lock4ComboBox4.Text Then
            
                MsgBox ("This Lock Reference Number" & vbCrLf & "Already Has Pricing In the System" & vbCrLf & vbCrLf & _
                "Please Select The EDIT Option" & vbCrLf & "Or Enter Alternative Lock Reference"), vbExclamation + vbOKOnly
                Me.Lock4ComboBox4.Value = ""
            
        Exit For
        End If
    Next
End If
End Sub

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,186,764
Messages
5,959,625
Members
438,438
Latest member
Comunista_CZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top