dynamic range defined in vba code, how to specify as RowSource

[mdavid]

Hi I have a Sub routine where I define a range, this range changes depending on which cell is clicked - ActiveCell.Offset.
I have a UserForm Listbox and would like to specify the above range as the RowSource, how do I do that?
Thanks for any help
David

 

[GraH - Guido]

Hi mdavid and welcome to this club. Can you please add a sample file. It will allow us to help you much quicker. You also may want to check out the best practises of the forum. (just saying, no harm intended )

 

[mdavid]

Hi, Thanks for your reply, I worked it out in the end, here's my code if anyone's interested.
I've selected a range where the value in the clicked column is the same, sorted the range from 3 columns and loaded some of the columns into a listbox

Private Sub UserForm_Initialize()
Dim frstRow As Long, lstRow As Long, cnt As Long, i As Long, j As Long
Dim objTable As ListObject
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 & ":K" & lstRow)
    With rng
        .Sort Key1:=.Range("Complaints!D" & frstRow & ":D" & lstRow), Order1:=xlAscending _
             , Key2:=.Range("Complaints!E" & frstRow & ":E" & lstRow), Order1:=xlAscending _
             , Key3:=.Range("Complaints!F" & frstRow & ":F" & lstRow), Order1:=xlAscending, Header:=xlGuess
    End With
   Set rng = Application.Range("Complaints!A" & frstRow & ":K" & lstRow)
       i = 0
    i = lstRow - frstRow
  ' this for loop selects columns to load to listbox
    For j = 0 To i
             ListBox1.AddItem rng.Cells(j + 1, 4)
             ListBox1.List(j, 1) = rng.Cells(j + 1, 5)
             ListBox1.List(j, 2) = rng.Cells(j + 1, 6)
             ListBox1.List(j, 3) = rng.Cells(j + 1, 9)
             ListBox1.List(j, 4) = rng.Cells(j + 1, 11)
     Next j
    ' Below is for moving the whole range into the listbox
    ' UserForm8.ListBox1.RowSource = rng.Address
    ListBox1.ListIndex = -1
    Me.StartUpPosition = 0
    Me.Top = 50
    Me.Left = 250
End Sub