Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Clicking the last list item in a combo box throws "run time error '2147352571 (80020005)': "and doesnot display the next form as intended

0

Hi,

I am using a combo box and based on the value I choose and click on a command button the respective user form should be displayed.Forms are displayed for the first 3 list values.But when I choose "Deboarding" I am getting an error as follows "run time error '2147352571 (80020005)': Could not set the value property.Type mismatch".

Can someone please help on this error?

Code for combo box is below:

Reqtype.List = Array("WI", "Asset_Refresh", "Onboarding", "Deboarding")

Code for command button is below:

Private Sub Load_Request_Click()

If Request_Type.Reqtype.ListIndex = 0 Then

WI_Sheet.Show

ElseIf Request_Type.Reqtype.ListIndex = 1 Then

Asset_Refresh_Sheet.Show

ElseIf Request_Type.Reqtype.ListIndex = 2 Then

Onboarding_Sheet.Show

ElseIf Request_Type.Reqtype.ListIndex = 3 Then

Deboarding_Sheet.Show

End If

End Sub

Thanks,

Pooh

Answer
Discuss

Answers

0

I suggest you replace your existing code with the one below. As you see, the size of the Click event procedure has shrunk drastically but has been replaced with the (comparatively) huge support function DisplayUserForm.  This function is so much more voluminous than your code because it does so many things your code doesn't, namely,  it tells you if the ListIndex =-1 (nothing is selected) and if checks if the form is loaded and hidden and displays it even in that case instead of crashing. By the way, it also reduces the source of form names from 5 to 1, and that is 4 potential error sources less - much easier to maintain and modify.

Private Sub Load_Request_Click()
    ' 133
    Const ReqType   As String = "WI,Asset_Refresh,Onboarding,Deboarding"
    Dim Idx         As Integer          ' index to Request_Type/ReqType

    Idx = Request_Type.ReqType.ListIndex
    If Idx >= 0 Then
        If Not DisplayUserForm(Split(ReqType, ",")(Idx)) Then Exit Sub
    End If
End Sub

Private Function DisplayUserForm(FormName As String) As Boolean
    ' 133
    ' return Not True if the form couln'd be found or loaded
    ' adapted from http://www.cpearson.com/Excel/showanyform.htm

    Dim Form    As Object           ' loop object

    Debug.Print VBA.UserForms.Count
    For Each Form In VBA.UserForms
        If StrComp(Form.Name, FormName, vbTextCompare) = 0 Then
            Form.Show
            DisplayUserForm = True
            Exit Function
        End If
    Next Form

    With VBA.UserForms
        On Error Resume Next
        Set Form = .Add(FormName)
        If Err.Number Then
            MsgBox "The form """ & FormName & """ wasn't found.", _
                   vbInformation, "Invalid form name"
        Else
            Form.Show
        End If
    End With
End Function

I offer this code to you because you appear to suspect that your problem results from the ListIndex and its translation to a form name. I don't think so. The error message you get points at an error in loading the form Deboarding_Sheet. Is there an Initialize event procedure? The above code will eliminate one possible source. If the error persists we must then look at the next.

BTW, your use of periods in variable names (like ReqType.List) is confusing. VBA does fully support this but, in practice, most programmers leave the use of the period to be determined by the rules of syntax where ReqType would be an object and List its property. Of course,  Reqtype.List is not an object and therefore has no List property and therefore it's confusing.

You may also like to know that programmers have come to frown on so-called "snake names" which are created by using lots and lots of underscores. Load_Request_Click isn't more legible than LoadRequest_Click (just using caps to define the start of a word). In this example, Microsoft uses the underscore to separate the name of the event from the control's name. The underscore you added serves another purpose, and the conflation of the two purposes doesn't make for more clarity.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login