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 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 2

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

Answer 3

I found the answer here. You can declare:

Private Sub SetIntialToggleValue(ByRef ControlName As MSForms.TextBox)

Answer 4

Currently, you're not passing in the name of the textbox. You're passing in the textbox object itself. You did reference it as a Control in your method, but if you're only using textboxes, you could replace Control with Textbox. Indeed, Textbox is its own type.

Answer 5

The answer as I'm sure you have all figured out is given here: https://www.access-programmers.co.uk/forums/threads/passing-a-control-as-parameter.213322/

Calling a function with brackets causes the compiler to force a conversion to ByVal which replaces your "control" with the "value" of the control and passes it with the wrong type.

Correct answer is simply SetIntialToggleValue MyTextboxName