Where/how do I register a dynamic range

[davidmyers]

Hi I want to populate a userform combo/listbox - RowSource from a dynamic range that I created in a Sub according to which cell is clicked on (using activecell.offset). What do I specify for RowSource? Do I need to register the range name - how?
Thanks for any help
David

 

[Fluff]

Something like

ComboBox1.RowSource = MyRange.Address

 

[davidmyers]

Hi Fluff, Thanks very much for this.

Here's my code:

Private Sub CreateRowSource()
Dim frstRow As Long, lstRow As Long, cnt As Long
Dim rng As Range
   cnt = 0
    Do While (ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(cnt - 1, 0).Value)
       cnt = cnt - 1
    Loop
    frstRow = ActiveCell.Row + cnt
    cnt = 0
    Do While (ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(cnt + 1, 0).Value)
       cnt = cnt + 1
    Loop
    lstRow = ActiveCell.Row + cnt
    Set rng = Application.Range("Complaints!A" & frstRow & ":B" & lstRow)
    MsgBox "rng = " & "Complaints!A" & frstRow & ":B" & lstRow & " address: " & rng.Address
    UserForm8.ComboBox1.RowSource = rng.Address
    UserForm8.Show
End Sub

This is what I see in the message box:

"rng = Complaints!A459:B462 address: $A$459:$B$462"


My Combobox appears empty, also nothing appears in the RowSource property

There is a UserForm8 and a ComboBox1

What am I doing wrong?

Thanks
David

 

[Fluff]

Try putting that code into the Userform_Initialise event

Private Sub UserForm_Initialize()
Dim frstRow As Long, lstRow As Long, cnt As Long
Dim rng As Range
   cnt = 0
    Do While (ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(cnt - 1, 0).Value)
       cnt = cnt - 1
    Loop
    frstRow = ActiveCell.Row + cnt
    cnt = 0
    Do While (ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(cnt + 1, 0).Value)
       cnt = cnt + 1
    Loop
    lstRow = ActiveCell.Row + cnt
    Set rng = Application.Range("A" & frstRow & ":B" & lstRow)
    MsgBox "rng = " & "Complaints!A" & frstRow & ":B" & lstRow & " address: " & rng.Address
    Me.ComboBox1.RowSource = rng.Address

End Sub

 

[davidmyers]

Hi Fluff,
Forgot that combobox is a dropdown and list box displays the list. I've changed to a listbox and it works.
Apologies for wasting your time
Thanks for your help
David

 

[Fluff]

Glad to help & thanks for the feedback