possible problem with referencing an array

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Hi,
I have a listbox which I am trying to populate with an array.

the line of code I am using to do this is
Code:
listbox1.Column() = d()
My sub is stalling on this line though, with error message
Run-time error 91: Object variable or With block variable not set

This seems like an odd error message to me since
  • a - it's not in a With block
    b - I have dimensioned the array, and have filled it.

am I missing anything here? Perhaps I'm referencing the array wrong. When I rest my cursor over that line in debug mode the comment that pops up shows that the error refers to the array reference.

I'm sure it's possible the problem may lie in another part of my code but I thought I'd try this first

Any help would be much appreciated.
Thanks for your time.

Straws
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
hello,
Try using the list property as follow

Sub fillit()
Dim vari As Variant
vari = Array("Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi")
ListBox1.List = vari

End Sub
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Thanks for taking the time to reply sunnyland

I had tried that before.... but with the same result.

I wanted to use the .column since it transposes the array... and in this case I need that.

Still a bit baffled

Straws
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello again,

I tried with a 2 dimension array and it works for me: check the dimension of your array may be:

Dim myarray(0 To 5, 0 To 3) As String

myarray(0, 0) = "Name"
myarray(1, 0) = "Surname"
myarray(2, 0) = "Age"
myarray(3, 0) = "Address"
myarray(4, 0) = "Town"
myarray(5, 0) = "Country"

myarray(0, 1) = "Jeff"
myarray(1, 1) = "Proud"
myarray(2, 1) = "22"
myarray(3, 1) = "Rue des Soupirs"
myarray(4, 1) = "Beziers"
myarray(5, 1) = "France"


myarray(0, 2) = "Jwen"
myarray(1, 2) = "Lion"
myarray(2, 2) = "36"
myarray(3, 2) = "Road to nowhere"
myarray(4, 2) = "London"
myarray(5, 2) = "England"

myarray(0, 3) = "Jwennie"
myarray(1, 3) = "Lionne"
myarray(2, 3) = "16"
myarray(3, 3) = "Road to where"
myarray(4, 3) = "London"
myarray(5, 3) = "England"

ListBox1.Column() = myarray
End Sub


Same result can be done easier using a range let's say using a named range called mydat corresponding to the data :
Book1
BCDEF
19NameSurnameAgeAddressTown
20JeffProud22RuedesSoupirsRoadtonowhere
21JwenLion36BeziersLondon
22JwennieLionne16RoadtowhereLondon
Sheet1



now to populate your listbox one sentence is enough:

ListBox1.Column() = WorksheetFunction.Transpose(Range("mydat"))

and you get the same result as prior way.

If you don't want the user to see or modify the listbox source then put them on a different worksheet and set property to not visible
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912

ADVERTISEMENT

Hello again :

in fact using

ListBox1.List() = WorksheetFunction.Transpose(Range("mydat"))

and

ListBox1.column() = WorksheetFunction.Transpose(Range("mydat"))

allows to joggle between the transposition
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello may be went a bit too fast for the answer in fact using:

this 2 subs will transpose your listboxsource

Sub columnheader_To_rowheader()
ListBox1.Column() = WorksheetFunction.Transpose(Range("mydat"))
End Sub

Sub rowheader_To_columnheader()
ListBox1.List() = WorksheetFunction.Transpose(Range("mydat"))
End Sub

Sorry for my explanation before.

Try this out having :
Book1
BCDEF
19NameSurnameAgeAddressTown
20JeffProud22RuedesSoupirsRoadtonowhere
21JwenLion36BeziersLondon
22JwennieLionne16RoadtowhereLondon
Sheet1


something like that
and run each one after naming the range mydat .

You will see that the list box change transposition
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Hi sunnyland.
sorry I haven't replied for a while - been away from this project at work.

Thanks for your replies, in comparing your code to mine I think it's possible there's something wrong with the dimensioning of my listbox... namely cause I you haven't dimensioned the listbox in your code...

at the moment I have
Code:
Dim listbox1 As MSForms.ListBox

could this be interferring with things?
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Hello again!

It's fixed!
I hadn't fully grasped a couple of basic concepts and hadn't made a userform with a listbox1 on it :oops: :oops: :oops:
err. yes so... I thought I could just do it from inside the macro... by dimensioning it and a few other things, but actually I needed to make a whole new form.

It all makes sense now. Sorry to drag you along with that wild goose chase sunnyland.

Thanks for trying to help all the same!

Straws
 

Forum statistics

Threads
1,137,337
Messages
5,680,904
Members
419,939
Latest member
AJWildOne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top