1

I have 2 sheets (Sheet1,Sheet2), which contain different data. In VBA, I have 2 listbox, one for display the Column A (Sheet1) and one for display the Column A (Sheet2). When I execute my coding,the listbox1 and listbox2 keep on showing the ActiveSheet Column A's Data, not Sheet1's data or Sheet2's data separately.

With ListBox1
    .ColumnCount = 3
    .ColumnWidths = "50"
    .RowSource = Sheets("Sheet1").Range("A:A").Address
End With

With ListBox2
    .ColumnCount = 3
    .ColumnWidths = "50"
    .RowSource = Sheets("Sheet2").Range("A:A").Address
End With
Community
  • 1
  • 1
whywhy
  • 155
  • 1
  • 6
  • 13

1 Answers1

0

When you say Sheets("Sheet1").Range("A:A").Address and Sheets("Sheet2").Range("A:A").Address, It will always give you $A:$A and it will refer to the activesheet.

Try this instead

.RowSource = Sheets("Sheet1").Name & "!" & _
             Sheets("Sheet1").Range("A:A").Address

or 

.RowSource = Sheets("Sheet1").Name & "!" & "A:A"

Similarly for Listbox2

.RowSource = Sheets("Sheet2").Name & "!" & _
             Sheets("Sheet2").Range("A:A").Address

or

.RowSource = Sheets("Sheet2").Name & "!" & "A:A"

BTW I recommend finding the last row of the column using THIS and then creating your .RowSource

.RowSource = Sheets("Sheet1").Name & "!" & _
             Sheets("Sheet1").Range("A1:A" & LRow1).Address

or

.RowSource = Sheets("Sheet1").Name & "!" & "A:A" & LRow1

Similarly for Listbox2

.RowSource = Sheets("Sheet2").Name & "!" & _
             Sheets("Sheet2").Range("A1:A" & LRow2).Address

or

.RowSource = Sheets("Sheet2").Name & "!" & "A:A" & LRow2
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Done,Thank Siddharth for your advice too. – whywhy Feb 17 '14 at 07:27
  • What is the use of ! in (.RowSource = Sheets("Sheet2").Name & "!" & "A:A" & LRow2) situation? i can't find it at website. – whywhy Feb 17 '14 at 08:08
  • By specifying `!` we are telling excel where does the `A:A` column is. If we do not do that then it will assume that you meant `activesheet` – Siddharth Rout Feb 17 '14 at 08:12
  • Try referencing a cell in Sheet1 to another cell in Sheet2. Note the formula and you will know – sam092 Feb 17 '14 at 08:13
  • 1 more thing is,How to select the rowsource start from A2 to the last row instead of A1. I get error when i try .RowSource = Sheets("Sheet2").Name & "!" & "A2:A" & LRow2 – whywhy Feb 17 '14 at 08:39
  • You need to find last row `LRow2` first as shown in the link in my answer – Siddharth Rout Feb 17 '14 at 08:43