Selected Answer
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.