CODEWITHSUNDEEP
excelvba
AlanH
AlanH

Reputation: 11

Excel VBA form TextBox passed as an Arguement

I'm trying to pass a TextBox created on a form to a subroutine to inspect the contents of the TextBox and limit the TextBox to numeric data only. Since VBA doesn't have overloading I'm using optional parameters to call the function without any parameters as part of TextBox_Exit event or as a standalone call with the textBox in question being passed as a parameter (I'm having to work around the fact that a TextBox_Exit event isn't thrown if the focus transfers to a control outside of the current frame).

The problem I'm having is when I use this code:

Private Sub fLaser_Exit(ByVal Cancel As msForms.ReturnBoolean)
    OnlyNumbers (tbLaserCutLength)
    OnlyNumbers (tbPartWidth)
    OnlyNumbers (tbPartLength)
    OnlyNumbers (tbLaserBendQty)
    oPart.LaserCutLength = CDec(tbLaserCutLength.Value)
    oPart.SheetMetalWidth = CDec(tbPartWidth.Value)
    oPart.SheetMetalLength = CDec(tbPartLength.Value)
    oPart.LaserCutLength = CInt(tbLaserBendQty.Value)

    UpdateCosts
End Sub

Private Sub OnlyNumbers(Optional ByRef tb As msForms.TextBox = Nothing)
    On Error GoTo ErrorHandler:
    If (tb Is Nothing) Then
        Set ac = ActiveControl
        Do While (TypeOf ac Is msForms.Frame)
            Set ac = ac.ActiveControl
        Loop

        If TypeOf ac Is msForms.TextBox Then
            With ac
                If Not IsNumeric(.Value) Then
                    .Value = 0
                    End If
                End With
            End If
        Else:
            With tb
                If Not IsNumeric(.Value) Then
                    .Value = 0
                    End If
                End With
        End If
    ErrorHandler:
End Sub

The problem is with the OnlyNumbers(tbLaserCutLength) call (and subsequent OnlyNumber calls. I'm getting a Run-time error '424': Object required error. tbLaserCutLength is an TextBox created using the Excel VBA Form editor. If I don't pass any paramaters to OnlyNumbers the subroutine works exactly as expected.

If I place a watch on tbLaserCutLength the object browser is sayins a object/TextBox not simply a TextBox. I suspect the problem is the msForms.TextBlock type used in the subroutine declaration. I've tried just TextBlock and I get the same results. Is there another parent class I should be using? Or, am I doing something else radically wrong?

Upvotes: 1

Views: 927

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Remove the brackets!!!

OnlyNumbers (tbLaserCutLength) should be OnlyNumbers tbLaserCutLength.

With the brackets you are forcing it to try and convert the object to a value, and then passing that value to your subroutine, but your subroutine is expecting an object.

Upvotes: 3

Related Questions