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

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