Initialize Combobox without Activating Sheet

[mucah!t]

Hello all,

I trying to initialize a combox with data from another sheet.
The combobox is on a userform.

The following code alone will populate the combobox with data from the active sheet, even thought it is asked to use worksheet "TEST"

With Worksheets("TEST")
ComboBox1.RowSource = .Range("c6", .Range("C6").End(xlDown)).Address
End With

By activating the sheet "TEST" it works but I'd like the userform to stay on the sheet where it was opened.

Sheets("TEST").Activate
With Worksheets("TEST")
ComboBox1.RowSource = .Range("c6", .Range("C6").End(xlDown)).Address
End With

What amI doing wrong?

this is the whole code:

Private Sub UserForm_Initialize()
 
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
 
    Set rngSource = Sheets("DATABASE").Range("E1", Sheets("DATABASE").Range("X1").End(xlDown))
 
    Set lbtarget = Me.ListBox1
    With lbtarget
        .ColumnCount = 20
        .ColumnWidths = "100;50;0;50;50,50,50,30,30,30,30,30,30,30,30,30,30,100,0,30"
        .List = rngSource.Cells.Value
    End With
 
    ListBox1.ListIndex = 0 'Select first row on listbox
 
'----- INTIALIZE COMBOBOX ------
Sheets("TEST").Activate
With Worksheets("TEST")
ComboBox1.RowSource = .Range("c6", .Range("C6").End(xlDown)).Address
End With
 
End Sub

 

[RoryA]

You are passing a string, but the address string does not include the sheet name. Either use:

With Worksheets("TEST")
ComboBox1.RowSource = "TEST!" & .Range("c6", .Range("C6").End(xlDown)).Address
End With

or I prefer:

With Worksheets("TEST")
ComboBox1.List = .Range("c6", .Range("C6").End(xlDown)).Value
End With

and don't use the RowSource at all.

 

[mucah!t]

Thank you Rorya for solving and explaining the problem.
Your suggestions work fine