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.

 

[nparsons75]

In my database (column F) I have currently 2 values. TEST1 and TEST2.

In column 9 I have values "pass" and "fail" Test1 = pass and Test2 = fail.

When I open the form and select the dropdown combobox test1 and test2 appear in the box. It should only show Test1 as that is equal to "pass".

When I open the userform the the database is not open until i press submit. How will the combobox check the column? Or does it check on opening the userform. The userform will be open constantly so not sure how it will work, please excuse my lack of vba knowledge and thanks for the help.

 

[dmt32]

in code you posted there is this line:

' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\database_np_201403190805.xlsx", _
                                      False, True)

So if this is the userform you are referring to then your database is open

As written your code should work providing you are testing for the value in the correct column.

Dave

 

[nparsons75]

But does that code execute when I press the submit button, therefore opening the database when I press submit which then opens, sends the data and then closes the database.

Column 9 (I) is the column that has pass & fail in it. Row 0, which row does this refer to? not sure how this part works. I have changed 9 to 10 etc but no difference. Test1 and Test2 still show in the combo box.

Nick

 

[dmt32]

Code executes when you show the form.

to see if you are looking at right column in your range try this code admendment:

For Each Item In rng
        Item.Offset(0, 9).Font.ColorIndex = 3
        If UCase(Trim(Item.Offset(0, 9).Value)) <> "PASS" Then
            .AddItem Item.Value    ' populate the listbox
        End If
    Next Item

what it will do is change Offset(0,9) range Font Colour to Red. You can then be certain you are working with correct column.

Dave.

 

[nparsons75]

I tried that be still no joy. Nothing changed to red?

I think I have a major problem. The user form is open all the time on the terminal. It is opened and then left open until the PC is rebooted (this could be weeks).

Its very difficult to explain. I have a number of terminals (PC's) located at different areas of a factory.

I have one main database that the userforms connect to. I also have the viewer which duplicates what is shown in the database.

When an operator types his information into the userform he presses submit. At that stage all the info is sent to the database.

At the next stage, the operator should see the axle serial number now appear in the combobox. This operator however has not opened the userform as it is already open.

Therefore will the data be seen?

I have tried the code you sent but still nothing. It isn't even doing the test, (red font).

I have no doubt I am doing something wrong and can only apologise.

I really need this to work. It will solve so many issues.

There may be a better way to approach this, if anyone has the answer please let me know.... Love excel, very frustrating though.

 

[dmt32]

This is excel yo are using not a relational database & will not update in real time - Your Ops will need to give it a bit of help. If form open all time then You will need to refresh the data to update status of your comboboxes.

After submit to remove an item from the combobox you can try this in your submit procedure:

Me.axlenumbox.RemoveItem (Me.axlenumbox.ListIndex)

place code just after wbDest.Close True statement.

If you add a refresh button on your form you should be able to run the Activate Code as folllows:

Private Sub Refresh_Click()
Call UserForm_Activate
End Sub

Dave

 

[nparsons75]

It appears to be working fine with this line:

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

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".

 

[dmt32]

post copy in your dropbox - I'm off to get a tooth fixed & will have a look when I return.

Dave

 

[nparsons75]

It did not work. No error and submitted ok, after refresh etc, the combobox still has the data. I entered "testing123" in axlenumbox. Submitted and refreshed. "testing123" still remains in axleboxnum.

Is it because axleboxnum looks at the database and brings in the data from their.

I can only apologise. I appreciate I am pushing excel. Just feel I am so close to the result.

 

[nparsons75]

I have added the dropbox link. There is 5 files. The database, the database viewer and 3 user forms. User forms in stages 1 2 and 3.

I need stage 2 to see the axle serial numbers which are available in the oven stage. I also need to see in paint stage 3 the axle serial numbers which have value PASS in stage backpressstage 2.

Hope you see what I am trying to achieve. Any questions I try my best to answer. Really appreciate the help. The file locations will need changing in the userforms. (don't know why im telling you that, you probably told me!!! Sorry)


https://www.dropbox.com/sh/et3jt6ph941be2h/e-ZYHJ_QM9