Case Select not catching right column in Userform's Combobox

Good day,
I have the following code in my Userform:

Dim arrCat As Variant

Private Sub ComboBox1_Change()
ActiveCell = ComboBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()

    With Sheets("Pre-Solicitation")
        Select Case ActiveCell.Column
            
            Case 6: arrCat = Range("Strategy").Value
            Caption = "Strategy Selection"
                  
            Case 7: arrCat = Range("Type").Value
            Caption = "Type Selection"
            
            Case 8: arrCat = Range("Peer").Value
            Caption = "Peer Selection"
    
        End Select
    End With

    With Sheets("Evaluation")
        Select Case ActiveCell.Column
            Case 6: arrCat = Range("Peer").Value
            Caption = "Peer Selection"

        End Select
    End With

    With Sheets("Report")
        Select Case ActiveCell.Column
            Case 8: arrCat = Range("Peer").Value
            Caption = "Peer Selection"

            Case 12: arrCat = Range("Peer").Value
            Caption = "Peer Selection"
        End Select
    End With

    ComboBox1.List = arrCat
    
End Sub

I have different sheet but some information will be repeated in these sheets. When I bring my Userform on Case 6, it doesn't populate the right case Selection. Why is this code not functioning well?

I mean I am closing my "With" therefore should not be looking in my Sheets("Evaluation") on case 6 when I trigger Case 6 in Sheets("Pre-Solicitation").

Hi,

Maybe this :

Dim arrCat As Variant

Private Sub ComboBox1_Change()
ActiveCell = ComboBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()

    With Sheets("Pre-Solicitation")
        Select Case ActiveCell.Column
            
            Case 6: arrCat = .Range("Strategy").Value
            Caption = "Strategy Selection"
                  
            Case 7: arrCat = .Range("Type").Value
            Caption = "Type Selection"
            
            Case 8: arrCat = .Range("Peer").Value
            Caption = "Peer Selection"
    
        End Select
    End With

    With Sheets("Evaluation")
        Select Case ActiveCell.Column
            Case 6: arrCat = .Range("Peer").Value
            Caption = "Peer Selection"

        End Select
    End With

    With Sheets("Report")
        Select Case ActiveCell.Column
            Case 8: arrCat = .Range("Peer").Value
            Caption = "Peer Selection"

            Case 12: arrCat = .Range("Peer").Value
            Caption = "Peer Selection"
        End Select
    End With

    ComboBox1.List = arrCat
    
End Sub

When working with different sheets, make sure that you use .Range(xxx) instead of Range(xxx) to "link" the range to the proper sheet. Otherwise, the range refers to the active sheet.

Runtime Error '381':
Could not set the List Property. Invalid Property Array Index

In which sheet are your named range?

Maybe an other hint :
Let's say you are in your sheet "Pre-solicitation", cell A1. This is your ActiveCell.
Your Select Case doesn't validate if the ActiveCell is in sheet "Pre-sollicitation" or "Evaluation" or "Report". It only checks the column of the ActiveCell.

You might need to add an other verification such as

If ActiveCell.Parent.Name = "Evaluation" then
....
End if

To verify in which sheet is your ActiveCell.

Am I clear?

Ok, then this is not the problem an d you do not need to use .Range("xxxx").
Best practice, I would use Sheets("Control").Range("xxx").

What about my other suggestion ?

What is the code meant to do?

Is it supposed to populate ComboBox1 based on which sheet is active and which cell is selected in that sheet?

If it is then perhaps something like this, untested, code would work.

Private Sub UserForm_Initialize()
Dim arrCat As Variant

    Select Case ActiveSheet.Name

        Case "Pre-Solicitation"
            Select Case ActiveCell.Column

                Case 6: arrCat = Range("Strategy").Value
                    Caption = "Strategy Selection"

                Case 7: arrCat = Range("Type").Value
                    Caption = "Type Selection"

                Case 8: arrCat = Range("Peer").Value
                    Caption = "Peer Selection"
            End Select

        Case "Evaluation"
            Select Case ActiveCell.Column
                Case 6: arrCat = Range("Peer").Value
                    Caption = "Peer Selection"
            End Select

        Case "Report"
            Select Case ActiveCell.Column
                Case 8: arrCat = Range("Peer").Value
                    Caption = "Peer Selection"

                Case 12: arrCat = Range("Peer").Value
                    Caption = "Peer Selection"
            End Select

    End Select

    ComboBox1.List = arrCat

End Sub

Your With in that code doesn't do anything.

No need for 3 variables for the last row(s).

Again untested.

Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim arrCat As Variant
Dim arrStrategy As Variant
Dim arrType As Variant
Dim arrPeer As Variant

    LastRow = Worksheets("Control").Range("C2").End(xlDown).Row
    arrStrategy = Worksheets("Control").Range("C2:C" & LastRow).Value
    
    LastRow = Worksheets("Control").Range("D2").End(xlDown).Row
    arrType = Worksheets("Control").Range("D2:D" & LastRow).Value
    
    LastRow = Worksheets("Control").Range("E2").End(xlDown).Row
    arrPeer = Worksheets("Control").Range("E2:E" & LastRow).Value
    
    Select Case ActiveSheet.Name
    
        Case "Pre-Solicitation"
            Select Case ActiveCell.Column
                Case 6: arrCat = arrStrategy
                    Caption = "Strategy Selection"
                Case 7: arrCat = arrType
                    Caption = "Type Selection"
                Case 8: arrCat = arrPeer
                    Caption = "Peer Selection"
            End Select

        Case "Evaluation"
            Select Case ActiveCell.Column
                Case 6: arrCat = arrPeer
                    Caption = "Peer Selection"
            End Select

        Case "Report"
            Select Case ActiveCell.Column
                Case 8, 12: arrCat = arrPeer
                    Caption = "Peer Selection"
            End Select

    End Select

    ComboBox1.List = arrCat

End Sub