Nick Parsons

Nick Parsons

  • NA
  • 1
  • 1k

Remove item from combo box once submitted to a database

Mar 25 2014 5:13 AM
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:
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