Type mismatch problem

I am completely new to VBA and am trying to create a user form that will fill-in TextBoxes dependent on the value of ComboBoxes.

We have a dozen or so separate workbooks which this form will be placed in for data entry purposes.

The source of the data ranges for the ComboBoxes and TextBoxes is being kept in a separate workbook for ease in updating and editing the data.

I am getting an error msg:

Run-time error '-2147352571 (80020005)':
"Could not set the property value. Type mismatch"

When I click on Debug this is the line that becomes highlighted:

Me.TextBox1.Value = .Range("A5:A21").Offset(Me.ComboBox1.ListIndex, 1)

I have tried everything I could find googling this error,
changing the ListItem As Variant, i As Integer to i As Variant;
taking out the ListItem As Variant, i As Integer line all together;
adding a line with Textbox() As Variant...(Like I said, I am extremely new to VBA)..

When I look at the cells in both excel sheets - the cells are all "formatted" as General, so I am not sure what their data type is ...?

I REALLY would appreciate any suggestions..... PLEASE HELP!!

This is my code -

Public ListItems As Variant, i As Integer
Public SourceWB As Workbook


Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data_Entry")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.ComboBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox1.Value
ws.Cells(iRow, 3).Value = Me.TextBox2.Value
ws.Cells(iRow, 4).Value = Me.TextBox3.Value
ws.Cells(iRow, 5).Value = Me.TextBox4.Value
ws.Cells(iRow, 6).Value = Me.TextBox5.Value
ws.Cells(iRow, 7).Value = Me.TextBox6.Value
ws.Cells(iRow, 8).Value = Me.TextBox7.Value


'clear the data
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.ComboBox1.SetFocus

End Sub

Private Sub cmdClose_Click()
 Unload Me
End Sub


Private Sub UserForm_Click()

End Sub
Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer

Dim SourceWB As Workbook
    With Me.ComboBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off,
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("A5:A21").Value
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
        ListItems = Application.WorksheetFunction.Transpose(ListItems)
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item

    End With

End Sub

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = Worksheets("Data_Entry")
Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
            
    With SourceWB.Worksheets(1)

        Me.TextBox1.Value = .Range("A5:A21").Offset(Me.ComboBox1.ListIndex, 1)


    End With
End Sub

Dave and Roy,

Thank you both so much for responding to my plea for help!

Roy I used:

 Me.TextBox1.Value = .Range("A5").Offset(Me.ComboBox1.ListIndex, 1)

and it seems to be working like a charm!!
Here's what it looks like now:

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = Worksheets("Data_Entry")
Set SourceWB = Workbooks.Open("C:\DATAENTRY\EXCEL_DATA_ENTRY_WORKSHEETS\LOOKUP_DATA\LOOKUP_FIELDS.xls", _
            False, True)
            
    With SourceWB.Worksheets(1)

 Me.TextBox1.Value = .Range("A5").Offset(Me.ComboBox1.ListIndex, 1)

SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        Application.ScreenUpdating = True
    End With
End Sub

I need to do the same thing for 10 more pairs of ComboBoxes/TextBoxes - will having that many cause any problems do you think?

Once again, thank you both - it is truly appreciated!
Bernadette