Pass userform control to sub

Question

I've got two subs: cmdSafe and checkTime. I want to call subCheck and pass a userform control.

Private Sub cmdSafe_click()
    Call checkTime(ufTimes.txtBegin)
End Sub

Sub checkTime(cntrl as Control)
    'Do something
End sub

In this case, I'm getting an error when it tries to call the sub. Run-time error 424: Object required. I went to check ufTimes.txtBegin, but this gives the value of the textbox, 15.

I hoped to be able to solve this by changing the first sub to the following:

Private Sub cmdSafe_click()
    Dim ctl as control
    ctl = ufTimes.txtBegin

    Call checkTime(ufTimes.txtBegin)
End Sub

This gave me another error (Run-time error 91: Object variable or With block not set) on the line ctl=ufTimes.txtBegin, probably because it's trying to set ctl to be a value. How do I solve this? I want to pass a control through, and it should also be able to be a ComboBox for example

Same problem. I don't think that's weird because the line ctl = ufTimes.txtBegin is interpreted as control = "15" — Grafit
Is ufTimes your UserForm or your TextBox? It's the UserForm, right? What version of Excel? — cxw

cxw cxw · Accepted Answer · 2016-01-29T15:03:47

In Excel 2013, the equivalent seems to work fine — (edit) provided you use Set when assigning Control variables. I added a CommandButton and a TextBox to a blank userform, with code:

Private Sub CommandButton1_Click()
    Dim c As Control
    Set c = TextBox1                      '<-- works with "Set"
    Call DoSomething(UserForm1.TextBox1)  '<-- also works
End Sub

Sub DoSomething(c As Control)
    MsgBox c.Text
End Sub

One option would be to use TextBox instead of Control - be more specific if you can.

I have references set to VBA, Microsoft Excel 15.0 Object Library, OLE Automation, Microsoft Office 15.0 Object Library, and Microsoft Forms 2.0 Object Library.

Pass userform control to sub

1 Answers