Solved

listbox not populating all fields

Posted on 2013-10-30
9
221 Views
Last Modified: 2013-10-30
vba 2010 excel

I'am populating a listbox via an array:
Arr = objMyRecordset.GetRows
       objMyRecordset.MoveFirst

With UserForm2.ListBox1
.Clear
.Column = Arr
.ColumnCount = 36
'.RowSource = Arr
        .ListIndex = -1
End With


I'AM not getting all the columns to show in the listbox?

so i tried setting the rowsource to "Arr"

But keep getting errors .?


Thanksd
fordraiders
0
Comment
Question by:fordraiders
  • 5
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Try using List and transposing the array.
With UserForm2.ListBox1
    .ColumnCount = objMyRecordset.Fields.Count
    .List = Application.Transpose(Arr)
End With

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
.List = Application.Transpose(Arr)    error on this line <-----------
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Any particular error?

Does your recordset have any null fields?

How many records are in it?
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
error 13 typemismatch..
Does your recordset have any null fields?

"yes"

it varys...after each executing  request
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:fordraiders
Comment Utility
I'am using a temp variable to see if the arr is getting this value .
Dim strb As String

strb = objMyRecordset.Fields(15)
strb = strb

and it clearly is...
but once the array is populated it does not show in the listbox ?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
I think the problem might be the nulls.

Try rewriting your query so that they are replaced with blanks, or whatever is appropriate.
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
weird because i have other places in the data where the fields are null?
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Do you mean other places where you are populating listboxes with arrays that have null values?
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
Thanks, got me on the right track !
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now