Combo Box Issue

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 2016
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You need to filter out the serial numbers no longer required to be displayed. You can do this by adding text like "Completed" in a column for each row when completed data is submitted.

Not tested but adjust your code along following lines:
You will need to adjust the Column offset to where you place the text in your database & of course, update code in your userform that submits the the data.

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\database_np_201403190805.xlsx", _
                                      False, True)
        'set the data range
        Set rng = SourceWB.Range(SourceWB.Range("A5"), SourceWB.Range("A" & SourceWB.Rows.Count).End(xlUp))
        ' get the values you want
        SourceWB.Close False    ' close the source workbook without saving changes
        Set SourceWB = Nothing
        For Each Item In rng
            If Item.Offset(0, 12).Value <> "Complete" Then
                .AddItem Item.Value    ' populate the listbox
            Next Item
            .ListIndex = -1    ' no items selected, set to 0 to select the first item
        End With
        Application.ScreenUpdating = True
    End Sub

Dave
 
Upvote 0
Currently get an error "Next without for"

Cant get code wrap around....


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\database_np_201403190805.xlsx", _ False, True) 'set the data range Set rng = SourceWB.Range(SourceWB.Range("A5"), SourceWB.Range("A" & SourceWB.Rows.Count).End(xlUp)) ' get the values you want SourceWB.Close False ' close the source workbook without saving changes Set SourceWB = Nothing For Each Item In rng If Item.Offset(0, 12).Value <> "Complete" Then .AddItem Item.Value ' populate the listbox Next Item .ListIndex = -1 ' no items selected, set to 0 to select the first item End With Application.ScreenUpdating = True End Sub
 
Last edited:
Upvote 0
Try using code tags when posting code.

I omitted an Endif.

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\database_np_201403190805.xlsx", _
                                      False, True)
        'set the data range
        Set rng = SourceWB.Range(SourceWB.Range("A5"), SourceWB.Range("A" & SourceWB.Rows.Count).End(xlUp))
        ' get the values you want
        SourceWB.Close False    ' close the source workbook without saving changes
        Set SourceWB = Nothing
        For Each Item In rng
            If Item.Offset(0, 12).Value <> "Complete" 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
        Application.ScreenUpdating = True
    End Sub

Dave
 
Upvote 0
I now get a new error.

object does not support this property or method. on the line shown below.

Set rng = SourceWB.Range(SourceWB.Range("A5"), SourceWB.Range("A" & SourceWB.Rows.Count).End(xlUp))
 
Upvote 0
looks like its going to be one of those days!

try this:

Code:
With SourceWB.Worksheets(1)
        Set rng = .Range(.Range("A5"), .Range("A" & .Rows.Count).End(xlUp))
    End With

Adjust sheet index / name as required

Dave
 
Upvote 0
I appreciate the help. Do i change (1) to (database). Database being my worksheet.

Code:
With SourceWB.Worksheets(database)        Set rng = .Range(.Range("A5"), .Range("A" & .Rows.Count).End(xlUp))
    End With
 
Upvote 0
This is the code, i get an error still, really sorry to be a pain. Variable not defined. So close..


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\database_np_201403190805.xlsx", _
                                      False, True)
        'set the data range
        With SourceWB.Worksheets[COLOR=#ff0000][B](database)   [/B][/COLOR]
        Set rng = .Range(.Range("A5"), .Range("A6000" & .Rows.Count).End(xlUp))
    End With
        ' get the values you want
        SourceWB.Close False    ' close the source workbook without saving changes
        Set SourceWB = Nothing
        For Each Item In rng
            If Item.Offset(0, 12).Value <> "Complete" 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
        Application.ScreenUpdating = True
    End Sub
 
Upvote 0
you need to put double quotes (speech marks) around your worksheet name.

Rich (BB code):
With SourceWB.Worksheets("database")

Dave
 
Upvote 0
I have added all the code below.

I am getting the following error on this line: Set rng = .Range(.Range("A5"), .Range("A65536" & .Rows.Count).End(xlUp))

Application-defined or object defined error.

Must be close. I cant fathom it out.

Code:
'--------------------------------------------------------------------------------------- ' Module    : UserForm1
 ' DateTime  : 02/11/2005 13:49
 ' Author    : royUK
 ' Website   : [url=http://www.excel-it.com]royUK's Excel Site[/url]
 ' Purpose   : load a combobox from a closed workbook
 '---------------------------------------------------------------------------------------
Option Explicit
 
Private Sub AXLENUMCOM_Change()


End Sub


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\database_np_201403190805.xlsx", _
                                      False, True)
        'set the data range
        With SourceWB.Worksheets("database")
[COLOR=#ff0000][B]        Set rng = .Range(.Range("A5"), .Range("A65536" & .Rows.Count).End(xlUp))[/B][/COLOR]
    End With
        ' get the values you want
        SourceWB.Close False    ' close the source workbook without saving changes
        Set SourceWB = Nothing
        For Each Item In rng
            If Item.Offset(0, 12).Value <> "Complete" 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
        Application.ScreenUpdating = True
    End Sub






Private Sub clearbut_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub


Private Sub SUBMITBUT_Click()
Dim ws As Worksheet
Dim sFileName As String
Dim sFolderName As String
Dim LastRow As Long
Dim wbDest                      As Workbook
   Dim pad                         As Long
   Dim msg                         As String
   Dim Title                       As String




sFileName = "database_np_201403190805.xlsx"
sFolderName = "J:\WHEELSET FLOW SYSTEM\"




Application.ScreenUpdating = False
If Not Dir(sFolderName & sFileName, vbDirectory) = vbNullString Then
Set wbDest = Workbooks.Open(sFolderName & sFileName, ReadOnly:=False)
Else
pad = Len(sFolderName & sFileName) / 2
msg = MsgBox(sFolderName & sFileName & Chr(10) & Chr(10) & _
Space(pad) & "File Not Found", vbInformation, Title)
GoTo progend
End If




Set ws = wbDest.Sheets("Database")




Dim Foundcell As Range
    With ws
        Set Foundcell = .Columns(1).Find(Me.axlenumbox.Text, LookIn:=xlValues, lookat:=xlWhole)


        If Not Foundcell Is Nothing Then
            'update data from userform to worksheet ranges
            .Cells(Foundcell.Row, 11).Value = Me.axlenumbox.Text
            .Cells(Foundcell.Row, 12).Value = Me.wsettypecom.Text
            .Cells(Foundcell.Row, 13).Value = Me.bookedincom.Text
            '
            ' etc etc
            '
        Else
            MsgBox Me.axlenumbox.Text & Chr(10) & "Record Not Found", 48, "Not Found"
        End If
             
End With
wbDest.Close True
progend:
Application.ScreenUpdating = False
Unload Me
BACKPRESS.Show
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top