VBA (excel) - How do I feed UserForm Textbox to function?

Question

I have a sub that works to clear values from a userform textbox but I tried to convert it to a function so I can use it with multiple userforms.

Here's my userForm Code (the command button code)

Private Sub pdclear_Click()
    Dim passform As UserForm
    Set passform = NoteEntryForm1

    Dim inputtext As TextBox
    Set inputtext = frmBigInputBox

Call clear(passform, inputtext)
End Sub

Here is my function

Function clear(passform As UserForm, inputbox As TextBox)
      passform.inputbox.Value = vbNullString
End Function

Trying to execute returns "Type missmatch" error.

I set it as a userform and a textbox. What am I doing wrong?

thank you :)

I don't think that's your problem, but you should never use names that VB uses. IE Userform Userform, or Sheet Sheet. It confuses it sometimes. — David G
As @DavidGM points out, call your userform something else (not "userForm1", but like "theForm" or such). — BruceWayne
yes I thought about that. Really bad form. However, this still doesn't make this thing work. — Johnson Jason
the line highlighted yellow is this one "Set inputtext = frmBigInputBox" — Johnson Jason

Paul Ogilvie Paul Ogilvie · Accepted Answer · 2015-07-24T18:39:40

You define the function to have two parameters. Then in the function you concatenate the parameters as if one is a member of the other. That will give an error. To address the input box on the user form, use:

Function clear(passform As UserForm, inputbox As TextBox)
    passform.Controls(inputbox.Name).Value = vbNullString
End Function

or even more simple:

Function clear(inputbox As TextBox)
    inputbox.Value = vbNullString
End Function

(I have not been able to test this as I have no test module with forms in Excel.)

VBA (excel) - How do I feed UserForm Textbox to function?

2 Answers