adding set of specific range values in list box in respective columns

Question

I'm displaying set of range values in two list boxes using VBA form. In listbox1 headers, then per input displaying relevant data in listbox2 by using the below code:

'header
Set rng = sht.Range("A1:I1")

    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;40;30;120;160;120;50;30;30"
        .RowSource = rng.Address
    End With
'dynamic data 
Set rng1 = sht.Range(Cells(st, ukey), Cells(st + en - 1, utim))

With ListBox2
    .ColumnCount = 9
    .ColumnWidths = "50;40;30;120;160;120;50;30;30"
    .RowSource = rng1.Address
End With

How can I add the header and data in one listbox after the heading?

Also how can I add column 10 to 15 in the listbox from another set of ranges?

Vityata · Accepted Answer · 2018-03-22 13:40:12Z

2

How to add the header and data in one listbox after the heading?

Unite the two ranges, representing the header and the values like this

Set rng = Union(rng, rng1)
.RowSource = rng.Address

This is some small sample that works, combining both ranges into one listbox:

Private Sub UserForm_Activate()

    Dim rng         As Range
    Dim rngTitle    As Range

    Set rng = Range("D2:L3")
    Set rngTitle = Range("D1:L1")

    Set rng = Union(rng, rngTitle)

    With ListBox1
        .ColumnCount = 9
        .ColumnWidths = "50;40;30;120;160;120;50;30;30"
        .RowSource = rng.Address
    End With

End Sub

This is what I get:

edited Mar 22, 2018 at 13:40

answered Mar 22, 2018 at 12:30

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Linga Over a year ago

Thanks for your help :) I'm getting blank Listbox while trying the above code :(

Vityata Over a year ago

@Linga - this is a bit strange, probably you are missing something. Take a look at the updated answer it works quite ok for me.

Linga Over a year ago

I'm checking please wait

Linga Over a year ago

Any specific properties to be enabled?

Vityata Over a year ago

@Linga - nope. Just an empty userform with a ListBox1 in it. And some data in the activesheet in range "D1:L3".

|

Linga · Accepted Answer · 2018-03-22 15:55:31Z

I merged the ranges by using UNION as mentioned above by Vityata and moved them to a temp workbook and displayed by using the below code :)

Set rngt1 = sht.Range("A1:I1")
Set rngt2 = sht.Range("X1:AE1")
Set rngt3 = sht.Range("AI1")

Set rngd1 = sht.Range(Cells(st, ukey), Cells(st + en - 1, utim))
Set rngd2 = sht.Range(Cells(st, ccat), Cells(st + en - 1, etag))
Set rngd3 = sht.Range(Cells(st, comt), Cells(st + en - 1, comt))

Set rngt = Union(rngt2, rngt1)
Set rngt = Union(rngt3, rngt)
Set rngd = Union(rngd2, rngd1)
Set rngd = Union(rngd3, rngd)

Workbooks.Add
rngt.Copy ActiveSheet.Range("A1")
rngd.Copy ActiveSheet.Range("A2")

Set rng = ActiveSheet.Range(Cells(2, 1), Cells(en + 1, 18))
  

    With ListBox2
        .ColumnCount = 18
        .RowSource = rng.Address
        .ColumnHeads = True
    End With

Thank you Vityata for the help :)

Collectives™ on Stack Overflow

adding set of specific range values in list box in respective columns

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related