Type mismatch error while filling ComboBox ( Run-time error '-2147352571 (80020005)')

goncalogera

New Member
Joined
Nov 10, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Hi, I have a userform that needs to output the names of suppliers ("Fornecedores") and banks ("Banco") from a table in the respective combo boxes, FornecedorBox and BancoBox. For that I used the following loop and it has been working perfectly until I migrated the file from my personal computer to the company's computers... It throws the type mismatch error (Ive highlighted in the code the lines that throw an error) and I cant figure out where the error is. Ive tried putting the VAL() function but it just returns all zeros since they all throw an error.


Capturar.JPG

VBA Code:
Private Sub UserForm_Initialize()
''''''' Fill-in IDbox '''''''

    With ThisWorkbook.Sheets("Confirmings")
        Me.IDBox.Value = .Range("A" & Rows.Count).End(xlUp).Value + 1
    End With

''''''' Fill-in combo box with bank name from table ''''''

    Dim i As Long
    Dim n As Long
    Dim sh As Worksheet

    Set sh = ThisWorkbook.Sheets("Spreads")
    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

        For i = 2 To n
        With ThisWorkbook.Sheets("Spreads")
            [B][U]Me.BancoBox.AddItem sh.Cells(i, 1)[/U][/B]
        End With
        Next i

''''''' Fill-in combo box with supplier name ''''''

    Dim y As Long
    Dim f As Long
    Dim forn As Worksheet

    Set forn = ThisWorkbook.Sheets("Fornecedores")
    f = forn.Range("A" & Application.Rows.Count).End(xlUp).Row

        For y = 2 To f
        With ThisWorkbook.Sheets("Fornecedores")
           [B][U] Me.FornecedorBox.AddItem forn.Cells(y, 1)[/U][/B]
        End With
        Next y

End Sub
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Does this work?
VBA Code:
Me.Banco.List = Sheets("Spreads").Range("A2").Resize(n-1).Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,818
Office Version
  1. 365
Platform
  1. Windows
When you get the error look at the cell it's trying to add to the combo, what is it's value?
You will get that error if the cell contains an error value
 

goncalogera

New Member
Joined
Nov 10, 2020
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
For anyone that encounters this issue, my error was the AddItem which was not been converted to string. Simply adding CStr() solved my problem, so, in my case, the final result was, for the second box, Me.FornecedorBox.AddItem Cstr(forn.Cells(y, 1)).

Thanks for the help anyway!
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,818
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,310
Messages
5,595,401
Members
413,990
Latest member
Kher83

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
Top