Setting the rowsource of a listbox

[Laurence Lombard]

The statement
Userform1.ListBox1.RowSource = Sheets("Accs").Range("A2:B40")
results in an error "Type mismatch". I think the right hand side of the
equation must be a string, but I do not know how to construct the statement
so that it is of type string.
The rowsource is in a different sheet, so "A2:B40"" is not good enough.

I want to set different Rowsources for different columns, but this code does
not work.
If ActiveCell.Column = 9 Then
Set AccsList = Sheets("Accs").Range("A2:B110")
ElseIf ActiveCell.Column = 14 Then
Set AccsList = Sheets("Det").Range("A2:B50")
End IF
Userform1.ListBox1.RowSource = "AccsList"


Please help
Laurence

 

[Bob Phillips]

Morning Laurence,

You need to get the range address. In addition, make sure that AccsList is
dimensioned as a range object, and when you use an object, you don't enclose
in quotes. So in your code, use

Userform1.ListBox1.RowSource = AccsList.Address

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Laurence Lombard]

Thank you for your reply. This does not seem to work - AccsList.Address
returns only "A2:B110" (ie of the current worksheet). AccsList is in another
sheet!
Laurence

Userform1.ListBox1.RowSource = AccsList.Address

 

[Bob Phillips]

Laurence,

Sorry about that, forgot the sheet.

Try this version

Dim Accslist As Range
Dim sSheet As String
If ActiveCell.Column = 9 Then
Set Accslist = Sheets("Accs").Range("A2:B110")
sSheet = "Accs"
ElseIf ActiveCell.Column = 14 Then
Set Accslist = Sheets("Det").Range("A2:B50")
sSheet = "Det"
End If
UserForm1.ListBox1.RowSource = sSheet & "!" & Accslist.Address

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Leo Heuser]

Laurence

This line will do the job:

Userform1.ListBox1.RowSource = AccsList.Address(External:=True)

 

[Laurence Lombard]

Thank you very much Bob. You've put me on the right track. The code below is
possibly even shorter and more to the point and also works.
Laurence
-------------------------------------
Dim sList As String
If ActiveCell.Column = 9 Then
sList = "Accs!A2:B40"
ElseIf ActiveCell.Column = 14 Then
sList = "Det!A2:B40"
Else
MsgBox "Activecell may only be in column 9 or 14"
Exit Sub
End If
Userform1.ListBox1.RowSource = sList
----------------------------------------------

 

[Bob Phillips]

Laurence,

Yes that is the way I would have done it, but I kept your code as much as I
could. I do that so that the OP (that's you in this case<g>) can work out
the changes better.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)