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

J

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.
 
Joined
Sep 17, 2005
Messages
1
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top