Solved: Type Mismatch

[mphill]

I added the Option Explicit and I am now unable to get the names from an Excel sheet (column A, row 2-82) to my Word combobox. It throws an Error 13 type mismatch. I have tried changing the ListItems but no success.

Thanks in advance for any way to correct the situation.


Private Sub UserForm_Initialize()
Dim objExlApp As Excel.Application
Dim objExlWB As Excel.Workbook
Dim exlSheetAddresses As Worksheet
Dim exlRange As Range
Dim exlWasNotRunning As Boolean
Dim WbToWorkOn As String
Dim ListItems As Variant
Dim i As Integer
Dim projNumber As String

WbToWorkOn = "C:\Temp\GeologyNames.xlsx"
' WbToWorkOn = "g:\Geology\Shared\Word 2007\geologynames.xlsx"

On Error Resume Next
Set objExlApp = GetObject(, "Excel.Application")

If Err Then
exlWasNotRunning = True
Set objExlApp = New Excel.Application
End If

On Error GoTo Err_Handler

'objExlApp.Visible = False 'Not visible by default
Set objExlWB = objExlApp.Workbooks.Open(FileName:=WbToWorkOn)

Set exlSheetAddresses = objExlApp.ActiveWorkbook.Worksheets("Addresses")
'Populate the TO combobox, as more names are added to the
'spreadsheet the range number will need to be increased.
'Example from A2:A82 to A2:A90. Do this for the cbTo, cbFrom
'and the lbCC area.
With Me.cbTO
.Clear
Application.ScreenUpdating = False
cbTO.List = exlSheetAddresses.Range("A2:A82").Value
Application.ScreenUpdating = True
ListItems = objExlApp.WorksheetFunction.Transpose(ListItems)
'**'Throws error here
For i = 1 To UBound(ListItems)
.AddItem ListItems(i)
Next i
.ListIndex = -1
End With

If exlWasNotRunning Then
objExlApp.Quit
End If
Set objExlApp = Nothing
Set objExlWB = Nothing
Set exlSheetAddresses = Nothing
Set exlRange = Nothing

Exit Sub
Err_Handler:
MsgBox WbToWorkOn & " caused a problem." & Err.Description, vbCritical, _
"Error:" & Err.Number
If exlWasNotRunning Then
objExlApp.Quit
End If
End Sub

[Nelviticus]

It's because ListItems becomes a Variant of type Double and UBound only applies to arrays. You need to re-think your code because ListItems starts out as nothing and you then transpose it, ending up with something effectively meaningless. If I comment out the 'Transpose' line and the whole For/Next loop then run your code, my combo box is populated with the contents of cells A2 to A82 and I don't get any errors. I saw the following simple code the other day for finding how many non-empty rows there are in a column:
Dim EmptyRow As Long
EmptyRow = 2
Do While .Cells(EmptyRow, 2) <> ""
If .Cells(EmptyRow, 2) = "" Then Exit Do
EmptyRow = EmptyRow + 1
Loop
You could do that to find how many addresses you have then use:
cbTO.List = exlSheetAddresses.Range("A2:A" & EmptyRow - 1).Value
to get the range.

Regards

[mphill]

Thank you for the quick response. I will change the code accordingly.

[Nelviticus]

I've just spotted an omission in my code - where it says '.Cells(EmptyRow, 2)' it should say 'exlSheetAddresses.Cells(EmptyRow, 2)'. You should also replace the '2' with whatever column number you want to look in.

[mphill]

Got it in and working, thanks!