listbox itemselected code

Posted on 2006-04-25
Last Modified: 2012-06-27

I cant figure what is wrong with my code here.  What is supposed to happen is after a user updates the listbox by either highlighting it or un-highlighting it, a textbox will then be updated with a "total" from column 1.  what is actually happening is... if there are 3 items selected and there column 2 value is 2, 6, 20 the final result will be "6".  what is happening is that it is only getting the first value and adding itself once for every item selected.  so another example would be 10, 8, 25 my final value would be 30.  when it should be 43.  

Private Sub lstDieCount_AfterUpdate()
Dim varItm As Variant
Dim varDieNumber As Variant
Dim varDieNumberAdd As Variant

varDieNumber = 0
varDieNumberAdd = 0

'here i would like to add the numbers from column 1 for every item selected.
For Each varItm In Me.lstDieCount.ItemsSelected
      varDieNumberAdd = Me.lstDieCount.Column(1) ' Column 1 contains a number value.  could be any number.
      varDieNumber = varDieNumber + varDieNumberAdd ' this code is supposed to add the number value

Me.tbDieCount = varDieNumber 'this end result should report a total number.

End Sub
Question by:lightcross
    LVL 58

    Accepted Solution


    When you use .Column(), the first property is -- obviously -- the column number. However, it returns the value from the "selected" item, which happens to coincide with the first selection (the dotted line box). If you want to return the column 1 of another row, you need the second parameter:

        .Column( _column_ [, _row ] )

    In other words:

        .... = Me.lstDieCount.Column(1, varItm)

    LVL 2

    Author Comment

    Dang it!... I was trying it the other way around so I thought i was doing something wrong!  I was doing this:

      .... = Me.lstDieCount.Column(varItm, 1)

    FYI, I drove myself nuts on this one for at least a couple hours!
    LVL 58

    Expert Comment


    I distinctly remember writing one more comment. Something to the effect of "this is considered illogical" ( col,row instead of row,col ) ... I must have writting it and never clicked [Submit]!

    Anyway, glad to help and good luck with your project!

    Featured Post

    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

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    using Access 8 38
    Running Sum Issue 7 22
    Access 2013 combo box not working 3 11
    Create exported XLS from Query 19 13
    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 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

    18 Experts available now in Live!

    Get 1:1 Help Now