Runtime error when calling a subroutine with an object as argument excel vba

Question

Scenario

I have a userform whereby it has a Combobox with some options. There is a Textbox as well in the same userform. When I select a certain option in the combobox, I need the text box to be disabled as well as change background colour.

My Codes

Following are my codes. poType is the combobox name and unitPrice is the textbox name

Public Sub poType_Change()    
    If mainPage.poType.Value = "FOC" Then
        disabling (unitPrice)
    Else
        enabling (unitPrice)
    End If
End Sub

Following is the subroutines disabling and enabling

Sub disabling(ByVal objectToDisable As Object)
    objectToDisable.Enabled = False
    objectToDisable.BackColor = &H80000003
End Sub

Sub enabling(ByVal objectToEnable As Object)
    objectToEnable.Enabled = True
    objectToEnable.BackColor = &H80000005
End Sub

However, it is showing runtime error (424 object required) when I am executing this code. Anyone knows the reason?

Why is poType_Change public? What is mainPage? Is that the form? — Storax
– Storax, Commented Aug 4, 2018 at 6:53
poType_Change is public. mainPage is the userform where the combobox and textbox placed — Anu
– Anu, Commented Aug 4, 2018 at 6:55
Right, remove the "( ...)". It will evaluate the object to the default and that is the value of the textbox. You should also replace mainPage with Me. — Storax
– Storax, Commented Aug 4, 2018 at 6:57
@TimWilliams Thank you. It solved my problem with the above code — Anu
– Anu, Commented Aug 4, 2018 at 7:04

Anu · Accepted Answer · 2018-08-04 07:03:09Z

2

Able to find rootcause of this problem. The above problem can be solved in two ways

Method 1

Need to add call when calling a subroutine

Public Sub poType_Change()    
    If mainPage.poType.Value = "FOC" Then
        call disabling (unitPrice)
    Else
        call enabling (unitPrice)
    End If
End Sub

Sub disabling(ByVal objectToDisable As Object)
    objectToDisable.Enabled = False
    objectToDisable.BackColor = &H80000003
End Sub

Sub enabling(ByVal objectToEnable As Object)
    objectToEnable.Enabled = True
    objectToEnable.BackColor = &H80000005
End Sub

Method 2

Don't use parenthesis for arguments. But for this case, don't add call in front

Public Sub poType_Change()    
    If mainPage.poType.Value = "FOC" Then
        disabling unitPrice
    Else
        enabling unitPrice
    End If
End Sub

Sub disabling(ByVal objectToDisable As Object)
    objectToDisable.Enabled = False
    objectToDisable.BackColor = &H80000003
End Sub

Sub enabling(ByVal objectToEnable As Object)
    objectToEnable.Enabled = True
    objectToEnable.BackColor = &H80000005
End Sub

answered Aug 4, 2018 at 7:03

Anu

1,1392 gold badges21 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Storax Over a year ago

I recommend to also replace mainPage with Me!

Collectives™ on Stack Overflow

Runtime error when calling a subroutine with an object as argument excel vba

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related