Function taking ComboBox, ListBox and TextBox as parameter to clear them, gives type mismatch

Question

I'm new to VBA and I can't find a solution to this: I'm trying to write a function to clear a ComboBox, a ListBox and a TextBox, but I get the

Run time-error 13: Type Mismatch error

and I can't understand why.

I'm using the ActiveX controls.

'sub for resetting
 Sub Cancella(testo As TextBox, lista As ListBox, user As ComboBox)

    testo.Text = ""
    lista.Clear
    user.Clear

End Sub


  Sub CommandButtonReset_Click()
'Button locaed in an excelSheet called "Giustificativo"

'InputNumero1 is an textBox in "giustificativo
'ListArticoli1 is a listobx (same sheet)
'ComboBoxUtenti is a comboBox (ame sheet)
Call Cancella(InputNumero1, ListArticoli1, ComboBoxUtenti)

End Sub

I'm doing something wrong or it's something that it' not possible to do altogether? Thank you very much!

① your function does not return anything so it should be a `Sub` instead of a `Function`. ② you don't need `Call` just use `Clear myTextbox, myListbox, myComboBox` without `Call` and parenthesis. ③ Your variables `myTextbox` etc are `Nothing`. Please show how they are defined or show the real code instead of this fake code. Also tell where your boxes are located (eg userform or worksheet) and where your code `CommandButtonReset_Click` is located. — Pᴇʜ, Jan 29 '19 at 13:25
You're probably just bound to `Excel.TextBox`. Try changing `t As TextBox` to `t As MSForms.TextBox`. — Comintern, Jan 29 '19 at 13:25

Pᴇʜ · Accepted Answer · 2019-01-29T13:58:44.627

Note that there are two types of controls. Form Controls and ActiveX Controls. So you must be sure to declare the correct type eg MSForms.TextBox.
See What is the difference between "Form Controls" and "ActiveX Control" in Excel 2010?
And VBA MSFORMS vs Controls - whats the difference
Make sure you specify on which worksheet your controls are, so Excel does not guess which sheet you mean.

I recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

Option Explicit

Sub Cancella(testo As MSForms.TextBox, lista As MSForms.ListBox, user As MSForms.ComboBox)
    testo.Text = ""
    lista.Clear
    user.Clear
End Sub

Sub CommandButtonReset_Click()
    With Worksheets("Giustificativo")
        Cancella .InputNumero1, .ListArticoli1, .ComboBoxUtenti
    End With
End Sub

It works I edited the 'celle.Value = " part for the code in the question since I did not meant to put it in the first place, (I copied another part of code witouth realizing) I though if left there it could add unnecessary confusion for a user that have my same issue. — SingingRaven, Jan 29 '19 at 14:01

Function taking ComboBox, ListBox and TextBox as parameter to clear them, gives type mismatch

1 Answers1