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]

I wrote it this way:

Set rng = .Range(.Range("A5"), .Range("A" & .Rows.Count).End(xlUp))

Dave

 

[nparsons75]

My apologies, i though i needed to edit the range "A". I have put your code as it should be and now get this error.

Object required: on this line: For Each Item In rng


​Thank you so much.

 

[dmt32]

you are having fun with this.

Try moving following two lines of your code to just above Application.Screenupdating = True line.

    SourceWB.Close False
    Set SourceWB = Nothing
    Application.ScreenUpdating = True

Dave

 

[nparsons75]

I know have this working, well it does not error but not sure it is doing what i need. Stage 2 for example can see axle numbers in stage 1. Stage 3 can see only axle numbers that have been added to stage 2. The problem i have is that i need the numbers to move from stage to stage. So for example, when submitting at stage 2 the axlenumber will now appear at stage 3, but no longer at stage 2. I need this to happen whilst always maintaining the data in the database. Is this possible. This is the final piece of my jigsaw so to speak.

 

[dmt32]

in each userform stage you need to apply some filtering to ensure only those records that need to be viewed are added.

as an example from earlier code:

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

You could have Completed Field status as Stage 1 Completed and test for that.

The Database Viewer sho see all records.

Dave

 

[nparsons75]

Ok i see. I have changed "complete" to "pass" as that is the wording i will be using. What do the 0 and 12 represent? If Item.Offset(0, 12)

 

[dmt32]

offset(0,12)

the first part which is shown as 0 is for the Row. So with value of 0 you are using same row,

the second part which is shown as 12 is for the Column. With value of 12 this means you are looking 12 Columns to the right (Column M) where data is located.

Dave

 

[nparsons75]

This is my latest code.

Private Sub UserForm_Activate()    Dim SourceWB As Workbook
    Dim rng As Range, Item As Range
    Dim i As Integer
    Application.ScreenUpdating = False
    With Me.axlenumbox
        .Clear    ' remove existing entries from the combobox
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("J:\WHEELSET FLOW SYSTEM\LIVE SYSTEM\database_np_201403190805.xlsx", _
                                      False, True)
        'set the data range
        With SourceWB.Worksheets("database")
        Set rng = .Range(.Range("F5"), .Range("F" & .Rows.Count).End(xlUp))
    End With
        ' get the values you want
        
        For Each Item In rng
            If Item.Offset(0, 9).Value <> "pass" Then
                .AddItem Item.Value    ' populate the listbox
            End If
            Next Item
            .ListIndex = -1    ' no items selected, set to 0 to select the first item
        End With
        SourceWB.Close False    ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
    End Sub

I get no errors which is great but my combobox is not populating with the only values that equal "pass". It is showing all numbers available?

Hope you can assist, I am going bonkers....

 

[nparsons75]

Is it due to the fact that the database has not been opened until the submit button is pressed, therefore it has not populated the combobox. Hope that makes sense?

 

[dmt32]

assuming your range offset is looking at the correct column then you could try this:

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

This should remove case match & any leading or trailing space issues.

Dave