?
Solved

Getting ListBox items into a variable in VBA

Posted on 2011-05-05
12
Medium Priority
?
2,794 Views
Last Modified: 2012-05-11
I have a form that has a ListBox (called “Article”, and with no Control Source) containing only 4 items that I have typed in.
When the user selects on of these items I want the selected item to be assigned to a string value in my VBA.

I have tried using  [Article] and Article.value each of which always contain NULL regardless of what value actually shows in the ListBox.

I have also tried to include a ‘Control Source’ (to a field called [Article]) which always loads the associated table with only the first item from the ListBox.

Any ideas?
0
Comment
Question by:usatrfe
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 35696667
As long as its a ActiveX control you can use:-

ListBox1.List(ListBox1.ListIndex)

This will return the currently selected list value.
0
 
LVL 6

Expert Comment

by:bartvd
ID: 35696691
With this code you get the first item selected

ListBox.ItemData(ListBox.ItemsSelected.Item(0))
0
 
LVL 40

Expert Comment

by:als315
ID: 35696699
If your listbox have name Article, in afterupdate event for it you can get it's value:

Private Sub Article_AfterUpdate()
    MsgBox Me.Article
End Sub
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:usatrfe
ID: 35696849
I'm using Access 2007:-

ListBox1.List (as Article.List) is not valid (in suggetion ListBox.ItemData(ListBox.ItemsSelected.Item(0))

MsgBox Me.Article returns only NULL.

ListBox.ItemData(ListBox.ItemsSelected.Item(0)) returns Runtime error 2480

Any other ideas?
0
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 35696908
I've just tested the same conditions and Article.Value returns the currently selected list value.

What error are you getting when you try this method?
0
 

Author Comment

by:usatrfe
ID: 35696968
Runtime erro 94 Invalid use of NULL
0
 
LVL 40

Expert Comment

by:als315
ID: 35696989
Can you upload your form with this listbox?
0
 
LVL 6

Expert Comment

by:Russell Lucas
ID: 35697012
I suspect that the problem is not how you are accessing the control. Would you be able to post the code so that we can diagnose it please?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35697070
Sounds like you have set the listbox to be multi-select.
That would return a null value.

Do you need multi-select? If not then set it to none.  You will then get the selected value.
0
 

Author Comment

by:usatrfe
ID: 35697117
multi-select was set to NONE.

Here is the file.


UnitSerNo.accdb
0
 
LVL 6

Accepted Solution

by:
Russell Lucas earned 2000 total points
ID: 35697142
Here you go, you can't use Got_Focus to write the variable from the same control. It's not set at that point.

Changed to After_Update and tested.
UnitSerNo.accdb
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35697160
You need to move the code into the afterupdate event procedure.

Nothing is selected at the point of 'gotfocus'
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

840 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