Passing text box control to private sub/function

Question

I have a series of text boxes that I want to evaluate on form load to change some other elements of the form. Rather than processing them line by line I want to create a function. How can I pass the name of the text box to a function. For example:

Private Sub Form_Load()

    Call SetIntialToggleValue(MyTextboxName)

End Sub

Private Sub SetIntialToggleValue(ByRef ControlName As Control)

    MsgBox ControlName.Value

End Sub

This give me "Object required" error on the MsgBox line. Which makes me think I'm not passing the textbox control properly.

Answer 1

I found the answer here. You can declare:

Private Sub SetIntialToggleValue(ByRef ControlName As MSForms.TextBox)

Answer 2

I would ask for clarification if I had enough rep to comment, but here are some thoughts:

If you haven't explicitly declared your variable, MyTextboxName, it will be of type variant. You can't pass a variant to an object parameter. This might be enough:

dim MyTextBoxName as Control

But in this case, you wouldn't be working with names at all. If you want to work with names, you have to change your function. Change the signature to take a string and then use the string as the index into the Controls collection (if there is one on Access Forms):

Private Sub SetIntialToggleValue(ByRef ControlName As String)

    MsgBox Form.Controls(ControlName).Value

End Sub

Answer 3

How about something like the following:

Option Explicit
Dim ctl  as Control

Private Sub Form_Load()

    set ctl = Me.MyTextboxName
    Call SetIntialToggleValue(ctl)

End Sub

Private Sub SetIntialToggleValue(ByRef ControlName As Control)

    MsgBox ControlName.Value

End Sub