Combobox ROWSOURCE from a veryhidden sheet-CodePudding

I have a Combobox in a user form. I get the values for that Combobox from a specific column in my worksheet (Column T).

The Combobox is simply a list of insurance products and their pricing elements, it shows all products on the user form so that the user can select whatever products he wants.

The problem is that the list can grow or decrease (i.e. new products or deleting them). And to make things more complicated, the sheet that has the aforementioned values is located in a very hidden sheet (protected for confidentiality purposes). So whenever I open my userform, the Combobox doesn't show any data (i always have to open the hidden sheet so that they can be shown)

Here's the code

    Private Sub UserForm_Initialize()
    Products = Range(Range("T7"), Range("T7").End(xlDown)).Address
      UserForm11.produit.RowSource = Products
    End Sub

Whenever i add the Worksheets name (Worksheets("Calcul").Range(Range("T7"), Range("T7").End(xlDown)).Address) it generates an error.

I just want the combo box to be dynamic and fixed to the source mentioned in the hidden sheet. Any clues?

CodePudding user response：

Private Sub UserForm_Initialize()
dim Products as string
with sheets("Calcul")
 Products = .Range("T7", .Range("T7").End(xlDown)).address
end with
      UserForm11.produit.RowSource = "'Calcul'!" & Products
End Sub

another way, Products as range.

Private Sub UserForm_Initialize()
dim Products as range
with sheets("Calcul")
 Set Products = .Range("T7", .Range("T7").End(xlDown))
end with
      UserForm11.produit.list= application.transpose(Products)
End Sub

CodePudding user response：

The WorksheetFunction OFFSET() is made to create dynamic ranges. Creating a Dynamic Named Range in the Name Manager will allows us to see what range is being targeted. See: Dynamic Named Range. The Named Range or its formula can be used as the ComboBox's Rowsource. FYI: the Rowsource updates when the userform loads. You'll have to clear the Rowsource and reassign it to refresh the values after the userform is loaded.

InsuranceProductsList =OFFSET(Calcul!$T$7,1,0,COUNTA(Calcul!$T:$T)-COUNTA(Calcul!$T$1:$T$7),1)

Named Range Manager