ListBox.RowSource populating from active worksheet only

[RunTime91]

Greetings All

The following code will populate my ListBox from only the active worksheet even though I have stipulated to pull the data from a specific worksheet

Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Sheets("Addrs & OBs")
LrA = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = ("A2:A" & LrA)
End Sub

As always,

Thank You for any assistance

RT 91

 

[dreid1011]

Greetings All

The following code will populate my ListBox from only the active worksheet even though I have stipulated to pull the data from a specific worksheet

Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Sheets("Addrs & OBs")
LrA = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = ("A2:A" & LrA)
End Sub

As always,

Thank You for any assistance

RT 91

Well, you didn't really tell it to pull from that specific sheet yet, all you did was identify it. Maybe this:

Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Sheets("Addrs & OBs")
LrA = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = Ws1.Range("A2:A" & LrA).Address
End Sub

 

[RunTime91]

Hey Dreid ~

I gave it a shot and same results... I would have lost a large bet that your solution would not have worked

I know when I read it I just shook my head in disbelief that I missed that -

But the results are the same so now I'm really shaking my head.

My Code reads precisely as your solution code...

Any other ideas - I'm listening...

 

[RunTime91]

Hello All ~

I could still use some help with this one

Thanks Everyone ~

 

[RunTime91]

Still need some help with this...

I have tried the following to bypass the sheet variable and the ListBox will only populate the active sheet values...
EVEN though the Lr variable does reflect the correct row # for the targeted sheet.

Below is what I'm am now trying and it is still populating the ListBox from only the 'active sheet'

Set Ws1 = ThisWorkbook.Sheets("Addrs & OBs")
Lr = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
LrA = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
ListBox1.RowSource = ThisWorkbook.Sheets("Addrs & OBs").Range("A2:A" & LrA).Address

Thanks for any help...

 

[RunTime91]

Hello All...

Am I trying to do something that simply cannot be done?...

 

[dmt32]

Hello All...

Am I trying to do something that simply cannot be done?...

untested by try this update to your code & see if resolves your issue

Dim Ws1 As Worksheet
Dim LrA As Long
Set Ws1 = ThisWorkbook.WorkSheets("Addrs & OBs")
LrA = Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row
Me.ListBox1.RowSource = "'" & Ws1.Name & "'!" & Ws1.Range("A2:A" & LrA).Address

Dave

 

[RunTime91]

Dave ~ First ~ Thank You!!

I would have NEVER arrived at that solution

Your code has now been tested and works perfectly...!

That said, can you please help me understand why on the green earth of God, does MS require a crazy string solution to populate a ListBox?

I'm using the following for my ComboBox:

CmbTNotes.List . = Ws1.Range("A2:A" & LrOptCl).Value

I tried to adapt that to the list box and wound up here

Thanks Again Dave - Truly Appreciate it!

RT91

 

[dmt32]

That said, can you please help me understand why on the green earth of God, does MS require a crazy string solution to populate a ListBox?

Hi,

glad update resolved your issue but short answer to your question is that it does not. I was just showing how you can include your object variable Ws1 to reference the correct worksheet – using a variable in this way, the “crazy string solution” is needed which also allows for worksheet that may have a space in the name.

A simpler way when you are using the Address of a range object with RowSource would be to use the External parameter

Dim Ws1 As Worksheet

Dim rng As Range

Set Ws1 = ThisWorkbook.Sheets("Addrs & OBs")

Set rng = Ws1.Cells(2, 1).Resize(Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row)

' non activesheet

Me.ListBox1.RowSource = rng.Address(External:=True)

Rowsource is connected to the Range & has advantage that If data in the Range changes then the data in the ListBox will also update.
unless though, there is a need to do this (and / or you want to display column headers in the listbox) I would suggest that you use the List property of the control

Hope Helpful

Dave