VBA ComboBox Code Help

[ForumMember]

Hi all,

I have the code for my project which does almost what I want it to (thanks to Norie), but I have one small problem.

Option Explicit


Private Sub cboCause_Change()
Dim rngData As Range
Dim LastRow As Long

    If Me.cboCause.ListIndex <> -1 Then

        With Worksheets("Sheet1")

            LastRow = .Cells(Rows.Count, Me.cboCause.ListIndex + 2).End(xlUp).Row - 1

            Set rngData = .Cells(2, Me.cboCause.ListIndex + 2).Resize(LastRow - 1)

        End With

        Me.cboResponsible.List = rngData.Value
        Me.cboResponsible.ListIndex = -1

    End If

End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim NoHeaders As Long
Dim LastRow As Long
Dim arrHeaders
Dim arrIssue

    With Worksheets("Sheet1")
        NoHeaders = .Cells(1, Columns.Count).End(xlToLeft).Column

        arrHeaders = .Range("B1").Resize(, NoHeaders - 1).Value
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row

        arrIssue = .Range("A2:A" & LastRow).Value
    End With
    
    arrIssue = Application.Transpose(arrIssue)
    
    Me.cboIssue.List = arrIssue
    
    cboIssue.Value = ""
    
    Me.cboCause.List = Application.Transpose(arrHeaders)
    
    
End Sub

If I select an option in cboBox1 that only has one suboption, I get an error:

"Run-time '1004' Application-defined or Object-defined error"

When clicking on debug option, it highlights the following line of code:

Set rngData = .Cells(2, Me.cboCause.ListIndex + 2).Resize(LastRow - 1)

Could anyone explain why this might happen and how best to amend the code, or work around it to ensure it doesn't keep happening?

Many thanks in advance!

 

[ForumMember]

Any fresh eyes willing to give this one a look?