ArnoD1995
05-11-2016, 11:24 PM
Dear all,
I'm walking into some problems while executing a macro.
The macro runs on the 'UserForm_Initialize' event, and should fill a combobox (combobox1) with data/choices, from a list in an external database.
Problem is that it runs into different errors, i tried to fix this, solved some errors, but right now i'm getting stuck with error 440, automation error.
Sidenote is that i made some kinda 'dummydatabase' in which only 7 cells contain data (B2:B8).
In addition, when finished, the next step would be to not let the macro use a hardcoded file/filename, but the most recent/last modified file in folder. But, let's first make the current macro working, then i'll try to add the functionality of picking the most recent/last modified file in folder.
Well, here's the macro: (And yes, i did a lot of copy-catting ;) )
Option Explicit
Private Sub UserForm_Initialize()
Dim ListItems As Variant
Dim i As Integer
Dim SourceWB As Workbook
Dim listVal As Range
Dim srcLastRow As Long
'for testing purposes
Dim srcName As String
srcName = "X:\SolidWorks\Solidworks System Files\Databases\Klanten-Database NAV\Database.xlsx"
On Error GoTo exit_proc
With Me.ComboBox1
'Set the number of columns by code
.ColumnCount = 2
.Clear
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open(srcName, False, True)
'find the last row of data to prevent searching 1468 rows unnecessarily
'srcLastRow = SourceWB.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
For Each listVal In SourceWB.Sheets("Blad1").Range("B2:B8") '& srcLastRow)
.AddItem listVal.Value
'Offset(0,-1) gets second column of data from cell to the left
.List(.ListCount - 1, 1) = listVal.Offset(0, -1).Value
Next listVal
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
.ListIndex = -1
End With
exit_proc:
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
End Sub
I hope you can help me find the errors and/or mistakes in this code..
Thanks in advance,
Best regards,
Arno
I'm walking into some problems while executing a macro.
The macro runs on the 'UserForm_Initialize' event, and should fill a combobox (combobox1) with data/choices, from a list in an external database.
Problem is that it runs into different errors, i tried to fix this, solved some errors, but right now i'm getting stuck with error 440, automation error.
Sidenote is that i made some kinda 'dummydatabase' in which only 7 cells contain data (B2:B8).
In addition, when finished, the next step would be to not let the macro use a hardcoded file/filename, but the most recent/last modified file in folder. But, let's first make the current macro working, then i'll try to add the functionality of picking the most recent/last modified file in folder.
Well, here's the macro: (And yes, i did a lot of copy-catting ;) )
Option Explicit
Private Sub UserForm_Initialize()
Dim ListItems As Variant
Dim i As Integer
Dim SourceWB As Workbook
Dim listVal As Range
Dim srcLastRow As Long
'for testing purposes
Dim srcName As String
srcName = "X:\SolidWorks\Solidworks System Files\Databases\Klanten-Database NAV\Database.xlsx"
On Error GoTo exit_proc
With Me.ComboBox1
'Set the number of columns by code
.ColumnCount = 2
.Clear
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open(srcName, False, True)
'find the last row of data to prevent searching 1468 rows unnecessarily
'srcLastRow = SourceWB.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
For Each listVal In SourceWB.Sheets("Blad1").Range("B2:B8") '& srcLastRow)
.AddItem listVal.Value
'Offset(0,-1) gets second column of data from cell to the left
.List(.ListCount - 1, 1) = listVal.Offset(0, -1).Value
Next listVal
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
.ListIndex = -1
End With
exit_proc:
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
End Sub
I hope you can help me find the errors and/or mistakes in this code..
Thanks in advance,
Best regards,
Arno