Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Champaign, IL, USA, Illinois, USA
    Posts
    20
    Thanks
    0
    Thanked 1 Time in 1 Post

    VBA ListBox problem (Excel 2000 SR-1)

    ListBox value does not pick up its assigned value.

    I have not been able to figure out what goes wrong or how to avoid the following problem. Any help is appreciated.

    Several ListBoxes on a UserForm have unexpected and unexplained behavior associated with them. Each is initialized by using the RowSource property to reference a named range on a sheet in the open workbook.

    Then each ListBox gets an initial value. This is where the strange behavior occurs.
    Whether I set the value using the ListIndex or the Value Property, sometimes the Value property is shown as null (

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA ListBox problem (Excel 2000 SR-1)

    <this value is put into the string variable from a particular data record and is always assigned correctly>

    I think we need to be certain that the above is true. Could you post a sample of the actual lcode that picks up the value and assigns it to the ListBox.

    Does the strange behaviour happen when the sheet with the datasource is not the active sheet ?

    Andrew C

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Location
    Champaign, IL, USA, Illinois, USA
    Posts
    20
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA ListBox problem (Excel 2000 SR-1)

    The sheet is always active in this program so I don't know what would happen if the sheet were not selected. I can say from using the program many times, that the correct month has always been hightlighted when the form appears.

    The list of months in the ListBox is aways shown from a list on another spreadsheet. However, the selected month is assigned two ways. For new records, it is by reference to the current month. For existing records that are to be revised, by the month already entered in the record.

    The actual code from the ListBox Initialize follows.

    [Gobal constant and variable defined in a module declaration area but used here, among other places]
    Public Const gc_strSheet1Name As String = "Data Sheet"
    Global g_lngRow As Long '* Used in the EditRecord Subroutine AND
    '* the frmDataEntry form.

    ***********

    '* Be sure the correct sheet is active
    ThisWorkbook.Worksheets(gc_strSheet1Name).Activate

    Dim CheckCount As Integer
    Dim ctl As Control


    '* Create the Check objects
    CheckCount = 0
    FOR EACH ctl In frmDataEntry.Controls
    IF TypeName(ctl) = "CheckBox" Then
    ctl.Caption = Range("ServicesHold") _
    .Offset(rowoffset:=CheckCount, columnoffset:=0)
    ctl.GroupName = CheckCount + 1
    CheckCount = CheckCount + 1
    ReDim Preserve Checks(1 To CheckCount)
    Set Checks(CheckCount).checkgroup = ctl
    END IF
    NEXT ctl


    SELECT CASE g_lngRow
    '* Enter a new record
    CASE IS = 0
    '* Name the form for the purpose.
    frmDataEntry.Caption = "SW Statistics Data Entry"

    '* Set defaults for month as the current month,
    '* type as Inpatient, and intensity as unselected.
    lstMonth.ListIndex = Month(Date) - 1
    lstType.ListIndex = 0

    '* Edit an existing record
    CASE IS <> 0
    '* Name the form for the purpose.
    frmDataEntry.Caption = "SW Statistics Data Edit"

    '* Load the values from the record (not best practice to DIM here, but that

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA ListBox problem (Excel 2000 SR-1)

    Help me! I've fallen and I can't get up...

    I have a similar problem with a listbox glitch in VBA Excel2000.
    The code to initialize the listbox executes fine during the additem steps,
    but when I set the ListIndex to 0, the value of the listbox does NOT show the string,
    instead it shows "". BUT, the listbox displays correctly on the screen when SHOWed,
    (with the first line highlighted correctly).
    Then, selecting a different line with the mouse, then re-selecting the first line, seems to fix
    things, and the code that reads the selected value gets the string instead of "".
    The other couple listboxes work exactly right the whole time.

    My suspicion is that when I added some code (not having to do with this listbox),
    something broke behind the scenes. Because, when I chopped or commented
    out a bunch of the unrelated stuff, The problematic listbox started working right! and
    another listbox started having the same problem instead!

    Please give me a clue, anybody, how to track down the problem.
    Single step mode shows that the problem happens, but I don't know how to
    dig deeper when the initialization code setting ListIndex to 1, doesn't work.

    Another thread had some vague reference (superstitious) comment about
    globals causing a ruckus to userform stuff, and I use a few globals. But I haven't
    stumbled onto the problem if that is the cause.

    Am I trying to punch my way out of a paper bag with a blindfold on?

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA ListBox problem (Excel 2000 SR-1)

    Try using the .List property. See attached demo.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Feb 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA ListBox problem (Excel 2000 SR-1)

    My 3 userform listboxes are all populated by .Additem vba code with strings pulled from a large sheet (27000 rows, 30 columns, >5megabytes) and each gets a .Listindex set at a desired default item. And for debug I set the .Tag to the same string that was just .Additem'ed. Then, doing a Userform.Show displays exactly what it should. The listboxes all have the desired default item highlighted and all have all the items they should. (One funny is when I click the scroll down arrow, the box scrolls 3 items ONLY the first time this listbox is scrolled, and after that, scrolls one line like it should. Egad~!) Same for the other listbox with the large list (500 items).
    My real problem is that when I try to use the Listbox.Value before showing the form, (or after the user clicks OK, if the user has not changed the defaults), one or more Listbox.Values come back as "" instead of what they should have. It seems like excel is not realizing that it needs to stick a string into the .listbox.value place? Then when I try making mods to the code, the problem switches to a different listbox or listboxes without rhyme or reason.
    The Listbox.Tag has the correct string the whole time, but the .Value stays a null string.
    The only workaround is if the user selects a different item than the highlighted default, and then selects the default again, and then all the Listbox.Values come back with the actual strings they should have!! - HELP! my hair is falling out fast enough on its own without a problem like this.!

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA ListBox problem (Excel 2000 SR-1)

    See my answer to this question in the Excel forum.
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Jan 2002
    Location
    Champaign, IL, USA, Illinois, USA
    Posts
    20
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA ListBox problem (Excel 2000 SR-1)

    Having struggled with this problem for some time now, I have concluded that this is an undocumented bug.

    The Knowledgebase discusses a similar bug for ListBox Text Property Not Functional (Q181281). The workaround described there also works here. The main difference is that that article describes a problem where the Text property of a ListBox always returns an empty string, but my empty string comes and goes.

    The resolution in Q181281 is as follows.

    Use the List and ListIndex properties to return the text of the currently selected item. For example use

    Dim sMyString
    sMyString = List1.List(List1.ListIndex)

    Instead of

    Dim sMyString
    sMyString = List1,Text

    I found this workaround solved my problem with the empty Value property as well.

  9. The Following User Says Thank You to graeme2 For This Useful Post:

    Humble (2016-09-24)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •