re: VBA Problem - Passing a TextBox Object as a parameter

[jennie.eddy]

Thanks in advance for any help...

I'm creating a frontend form for a spreadsheet which uses a lot of
textboxes. I want to be able to pass any of these textbox objects to a
function which will alter the object's properties.

A simplified version of the code is shown below:


Private Sub TextBox1_Change()
Call makeRed(TextBox1) ' error 13
End Sub

Private Sub TextBox2_Change()
Call makeRed(TextBox2) 'error 13
End Sub

Private Sub makeRed(ByRef tb As TextBox)
tb.BackColor = vbRed
End Sub

I can get this to work using Visual Basic 5. But using VBA for Excel 97,
when I run I get:

Run-time error '13':
Type mismatch


It seems the TextBox's default value is being passed to the function, but I
want the object itself. This problem happens in Visual Basic 5 if I don't
use Call when calling the function.

How can this be done in VBA for Excel ? I've googled for answers but so far
drawn a blank.

 

[Eletron.Eletronics]

Try this:

Private Sub TextBox1_Change()
Call makeRed ( "TextBox1" )
End Sub

Private Sub TextBox2_Change()
Call makeRed ( "TextBox2" )
End Sub

Private Sub makeRed ( ByVal tbname As String )
Dim tb as TextBox
set tb = Controls ( tbname )
tb.SetFocus
tb.BackColor = vbRed
set tb = Nothing
End Sub