Passing controls from Excel to user-defined function

[Nick]

I have a worksheet that contains 2 TextBox controls, TextBox1 and
TextBox2

Somewhere on my worksheet a real-time event occurs where I want to
update the appropriate Text Box. The real-time event updates some
cell on my worksheet.

I create a user-defined function which simply takes the data in the
cell and
updates my text Box.

To solve my problem, I can simply create 2 user-defined functions.
However, each function hard codes the actual text box. If I have 20
TextBox controls on my worksheet, I would have to create 20 functions.
Not clean.

Ideally, I would like to create 1 function and pass the TextBox
control as a parameter from Excel into my user-defined function.

When I create the signature of my function as:

Public myfunc updateBox( myBox As MSForms.TextBox, ...)
{
myBox = <data>
}

results in the cell in my worksheet to display #VALUE!

suggestions?

thanks
Nick

 

[Harald Staff]

Hi Nick

Please post the function that somewhat works, hardcoded addressing included.
It would be easier to modify that to "general" than to make a non-working
pseudocode do something unknown.
(Looks like Java btw, my nightmares are instances of Java classes

Best wishes Harald

 

[Nick]

Currently, the following function is defined within a workbook
Module:

Public Function updateTextBox(ByRef myVal As Range) As String
ActiveSheet.TextBox1.Text = myVal

updateTextBox = "TextBox Updated."
End Function

As you can see, I'm making direct reference to the TextBox "TextBox1"

From Excel, I simply created a formula in any cell as:
=updateTextBox(A1)
for example.

When I did the following:

Public Function updateTextBox(ByRef myVal As Range, ByRef myBox As
MSForms.TextBox) As String

myBox.Text = myVal

updateTextBox = "TextBox Updated."
End Function

Nothing happens. That is, I see the error within my cell: #VALUE!
My cell was defined as: =updateTextBox(A1, TextBox1)


thanks,
Nick