Combobox not loading the items if the source sheet is not activated

Hi

In the attached workbook, I have a userform
the combobox ComboMainHead is being populated from the chart of accounts sheet. the problem is if the said sheet is not activated the combox is not populated.

What am I am doing wrong

Here is the code behind the userform

Private Sub UserForm_Initialize()
Me.lblHeader.Caption = Sheet8.Range("A7").Value & vbNewLine & Sheet8.Range("A8").Value & vbNewLine & Sheet8.Range("A9").Value
    Dim myCollection As Collection, cell As Range

    On Error Resume Next
    Set myCollection = New Collection
    With Me.ComboMainHead
        .Clear
        For Each cell In Sheet4.Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
            If Len(cell) <> 0 Then
                Err.Clear
                myCollection.Add cell.Value, cell.Value
                If Err.Number = 0 Then .AddItem cell.Value
            End If
        Next cell
    End With
    Me.ComboMainHead.ListIndex = 0

End Sub

Replace existing UserForm_Initialize code with the following one...

Private Sub UserForm_Initialize()
Me.lblHeader.Caption = Sheet8.Range("A7").Value & vbNewLine & Sheet8.Range("A8").Value & vbNewLine & Sheet8.Range("A9").Value
    Dim myCollection As Collection, cell As Range
    Dim lr As Long
    On Error Resume Next
    Set myCollection = New Collection
    lr = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
    With Me.ComboMainHead
        .Clear
        For Each cell In Sheet4.Range("A3:A" & lr)
            If Len(cell) <> 0 Then
                Err.Clear
                myCollection.Add cell.Value, cell.Value
                If Err.Number = 0 Then .AddItem cell.Value
            End If
        Next cell
    End With
    Me.ComboMainHead.ListIndex = 0

End Sub

Guru G

The problem is if I call the userform from the Database then the said combobox is not getting populated even with the amended code. This is what I just came to know. It should be populated if I call the userform from Database.

After making the changes, if I click the button called "Show Voucher Entry Form" on DataBase sheet, the ComboBox gets populated as desired.

Please refer to the demo video.

Works fine for me.

Private Sub UserForm_Initialize()
    With Sheet8
        lblHeader.Caption = .Range("A7").Value & vbNewLine & .Range("A8").Value & vbNewLine & .Range("A9").Value
    End With
    sn = Sheet4.Cells(1).CurrentRegion.Value
    With CreateObject("scripting.dictionary")
        For i = 3 To UBound(sn)
            If sn(i, 1) <> vbNullString Then x0 = .Item(sn(i, 1))
        Next
        ComboMainHead.List = .keys
    End With
    ComboMainHead.ListIndex = 0
End Sub

All you Gurus are correct at your place. If by means of the amended codes the ComboMainHead is populated then the second depended combobox is not populated.Please select the second item from the Mian Head and then click on sub Head combo.

The issue with both of your codes is because of the way you are trying to find the last used row in column A. Look at the bold part in the For Loop...
For Each cell In Sheet4.Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
This part is not qualified with the Sheet reference so it would always find the last row used in column A on the ActiveSheet.
So all you need is to make the same change in the ComboMainHead_Change code as well.

Try it like this...

Private Sub ComboMainHead_Change()
  Me.ComboSubHead.Clear
    Dim cell2 As Range
    Dim lr As Long
    lr = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
    For Each cell2 In Sheet4.Range("A3:A" & lr)
        If cell2.Value = Me.ComboMainHead.Value Then
          Me.ComboSubHead.AddItem cell2.End(xlToRight).Value
        End If
    Next
End Sub

Originally Posted by sktneer

The issue with both of your codes is because of the way you are trying to find the last used row in column A. Look at the bold part in the For Loop...
For Each cell In Sheet4.Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
This part is not qualified with the Sheet reference so it would always find the last row used in column A on the ActiveSheet.
So all you need is to make the same change in the ComboMainHead_Change code as well.

Try it like this...

Private Sub ComboMainHead_Change()
  Me.ComboSubHead.Clear
    Dim cell2 As Range
    Dim lr As Long
    lr = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
    For Each cell2 In Sheet4.Range("A3:A" & lr)
        If cell2.Value = Me.ComboMainHead.Value Then
          Me.ComboSubHead.AddItem cell2.End(xlToRight).Value
        End If
    Next
End Sub

Dhanayawaad Guru Ji!
That worked like a charm.much much appreciated.

Thanks to bakerman and others who tried their best to help out.

Is this what you mean ?

Private Sub ComboMainHead_Change()
  Me.ComboSubHead.Clear
    Dim cell2 As Range
    Dim lr As Long, x0
    lr = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
    With CreateObject("scripting.dictionary")
        For Each cell2 In Sheet4.Range("A3:A" & lr)
            If cell2.Value = ComboMainHead.Value Then x0 = .Item(cell2.Offset(, 1).Value)
        Next
        ComboSubHead.List = .keys
    End With
End Sub

You're welcome Imran! Glad we could help.
Thanks for the feedback.