Combo Box Issue

[nparsons75]

I have a combobox on a userform that gets its data from a closed spreadsheet. The main isssue is that a serial number is typed into a form that send it to a database. From the database the combobox picks up the serial number. Once the operation has been complete I need it to no longer show in the combobox but I cannot delete it from the database it it is always needed in there. Does anyone know of a workaround? appreciate any help.

 

[dmt32]

But not this one:

 If Item.Offset(0, 9).Value <> "PASS" Then

It returns all the data in column F5. It just does not seem to filter out any data that does not match "PASS".

In column 9 the data is equal to either "PASS" or "FAIL".

Offset 9 returns Column 10.

Try:

If Item.Offset(0, 8).Value <> "PASS" Then

Dave

 

[nparsons75]

Have you managed to look at the files? I will try this out shortly. Fingers crossed. I will let you know how it goes. I tried all different rows but made no difference, I am hoping I didn't try this one. Hope your tooth is OK. Sorry to be an additional pain.

 

[nparsons75]

Tried it, still the same. Still shows all numbers in column F.

Set rng = .Range(.Range("F5"), .Range("F" & .Rows.Count).End(xlUp))

Tried this:

For Each Item In rng            If Item.Offset(0, [B]8[/B]).Value <> "PASS" Then
                .AddItem Item.Value    ' populate the listbox
            End If

All values in column F are returned, the ones with "FAIL" still show?

Did you manage to run the files?

 

[dmt32]

The logic of the test is to additem if the value does not equal PASS. As FAIL does not equal PASS it will be added. You need to update your test to ensure only correct values are returned.

Dave

 

[nparsons75]

I have tested as follows. At stage 1 and 2 I have added data. Serial number 1 and 2. Number 1 is set to pass. Number 2 is set to fail. At stage 3 only number 1 should be visible to select. As it is, both 1 and 2 are present.

 

[nparsons75]

Just reading this again, I need the item to be added at the next stage if the value DOES equal PASS. A fail can go no further, PASS means it can proceed to the next stage. Is that the issue? Having said that I have tried both scenarios, and neither is working. I have no nails left....

 

[nparsons75]

does it matter that i have a combobox and not a listbox? I really cant believe this won't work, it all looks perfect.

 

[dmt32]

does it matter that i have a combobox and not a listbox? I really cant believe this won't work, it all looks perfect.

It should make no difference which you are using - you just need to work through the logical test (e.g Offset(0,8).Value <>"FAIL") in your For next Loop with each userform to ensure that only the required data populates the combobox.

 

[nparsons75]

Hi,

I have no hair left, I have pulled it all out......

I have tried the testing, changing the Offset(0,8).Value <>"FAIL" has no effect.

It seems to set the range above and then ignore this offset.

 

[dmt32]

Code will work if you have your logical test & offset correct.

I think with paint stage 3 try:

         For Each Item In rng
            If Item.Offset(0, 3).Value <> "PASS" Then
                .AddItem Item.Value    ' populate the listbox
            End If

This adds everything that is not equal to Ucase PASS

Backpress Stage

          For Each Item In rng
            If Item.Offset(0, 3).Value <> "AVAILABLE" Then
                .AddItem Item.Value    ' populate the listbox
            End If
            Next Item

This adds everything that is not equal to Ucase AVAILABLE.