VBA Problem - Passing a TextBox Object as a parameter

[jennie]

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) 'this gives error 13
End Sub

Private Sub TextBox2_Change()
Call makeRed(TextBox2) 'this gives 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 running it with VBA for Excel 97 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. (Note this problem also 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.

 

[tusharm]

You are very close. With Office 97, MS reorganized (once again) Office's implementation of menus/toolbars/userforms. Use msforms.Textbox and you'll be all set

Private Sub makeRed(ByRef tb As msforms.TextBox)

 

[CHatmaker]

Thx!

 

[elielCT]

i know its a little late but this might help u or sum1 else...
i was trying to pass form controls (text boxes) as variables in an array to then check the form and highlight anything incomplete..
the following is in a module that can be called to over and over..
if the form values are ok u get a value of TRUE returned.

Function checkForm(ByRef formObject, ByRef formItems)
Dim numItemsToCheck As Integer
Dim howManyOk As Integer
howManyOk = 0
numItemsToCheck = UBound(formItems, 1) + 1
''Dim formItems(2, 1)
''''formItems(0, 0) = "input1"
''''formItems(0, 1) = "text"
''''formItems(1, 0) = "input2"
''''formItems(1, 1) = "date"
''''formItems(2, 0) = "input3"
''''formItems(2, 1) = "integer"

For i = 0 To (numItemsToCheck - 1)
    Select Case formItems(i, 1)
        Case "text"
            If formObject.Controls(formItems(i, 0)).Text = "" Then
                formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 0)
                howManyOk = 0
            Else
               formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 255)
                howManyOk = howManyOk + 1
            End If
        Case "date"
            If formObject.Controls(formItems(i, 0)).Text = "" Then
                formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 0)
                howManyOk = 0
            Else
                If IsDate(formObject.Controls(formItems(i, 0)).Text) Then
                    If DateValue(formObject.Controls(formItems(i, 0)).Text) > DateValue(Month(Now) & "/" & Day(Now) & "/" & Year(Now)) Then
                        formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 255)
                        howManyOk = howManyOk + 1
                    Else
                        MsgBox ("Please enter a valid date.")
                        formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 0)
                        howManyOk = 0
                    End If
                Else
                    MsgBox ("Please enter a valid date.")
                    formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 0)
                    howManyOk = 0
                End If
            End If
            
        Case "integer"
            If formObject.Controls(formItems(i, 0)).Text = "" Then
                formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 0)
                howManyOk = 0
            Else
                If IsNumeric(formObject.Controls(formItems(i, 0)).Text) Then
                    formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 255)
                    howManyOk = howManyOk + 1
                Else
                    MsgBox ("Cost must be a number. Please check.")
                    formObject.Controls(formItems(i, 0)).BackColor = RGB(255, 255, 0)
                    howManyOk = 0
                End If
            End If
    End Select
Next
    
    
    
    If Not howManyOk = numItemsToCheck Then
        MsgBox ("You forgot to fill something out!")
        checkForm = False
    Else
        checkForm = True
    End If
End Function

the array as a comment is to demostrate the proper way to send parameters in case u ever need 2 reference.

and you would send all your form items to the module as follows:

Private Sub myForm_Click()
Dim formItems(4, 1) As String
    formItems(0, 0) = "textBox1"
    formItems(0, 1) = "integer" 'Value to check for in textBox1
    formItems(1, 0) = "textBox2"
    formItems(1, 1) = "text" 'Value to check for textBox2
    formItems(2, 0) = "textBox3"
    formItems(2, 1) = "date" 'Value to check for textBox3
    formItems(3, 0) = "textBox4"
    formItems(3, 1) = "integer" 'Value to check for textBox4
    formItems(4, 0) = "textBox5"
    formItems(4, 1) = "integer" 'Value to check for textBox5
    
    If FormModule.checkForm(formName, formItems) = True Then
       'Submit form code here.....
    End If
End Sub

the above array needs to be modified accordingly..
hope it helps sum1 as i ripped the little bit of hair i had left figuring this out...
PS: I have a slightly better one to handle any form items including comboboxes but its in .net.. so this can be tweeked for such...