Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Combo box filtering

  1. #1

    Question Combo box filtering



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a number of user forms. Let's say 5. Stage 1,2,3,4 and 5. At stage 1 a serial number is entered into a text box. At stage 2 the serial number becomes available in a combo box. I need to select the serial number from the combo box and press submit. I need the serial number to now show in a combo box at stage 3, BUT, no longer show at stage 2. Once the serial number has been submitted at stage 2 it can't be selected again (unless re-booked in at stage 1).

    It is like a production line. Stage by stage. Eventually when it reaches the end at stage 5 and submitted it will be gone from all combo boxes.

    Each user form opens up a database (spreadsheet) when submit is pressed. It enters the data and closes the database. I need all the data to remain in the database but the combo boxes to only show the serial numbers available to the stage.

    Hope this makes sense, this has bothered me for days, can't get to the bottom of it.

    This is my latest code for this operation:

    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 <> "fail" 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
    Last edited by nparsons75; 2014-03-25 at 10:18 AM. Reason: added code

  2. #2
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    need sample files (with notes) for testing

  3. #3

    Files

    I have only posted once so unable to send a link. Is there another way to get them to you?

  4. #4
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    upload files on sharing site then paste the link

  5. #5

    files

    it wont let me post a link?

  6. #6
    I can send after 5 posts

  7. #7

    More info

    Bit more info.

    How it works:

    1. Enter a serial number at stage 1 and select available in status.
    2. Select the serial number in stage 2, select pass or fail in status. Press submit.
    3. I now want the serial number (axle serial number) to appear in the combo box at stage 3. BUT no longer appear at stage 2.
    4. This happens at each stage afterwards.

  8. #8
    Please help....

    I have supplied a link to the files.


    Stages 1,2 and 3. There is a database file and a database viewer. (the viewer just duplicates the database for viewing purposes).

    Please ask if I need to explain in any other detail. Appreciate the help, thanks.

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

  9. #9
    Acolyte patel's Avatar
    Join Date
    Feb 2014
    Location
    Italy
    Posts
    59
    Articles
    0
    I asked for sample, not all project, I can not use it

  10. #10
    How would you like it.

    Do you need me to create it as a sample, excel sheet with userform?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •