VBA ListBox problem (Excel 2000 SR-1)

Topic

New Reply

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 (

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Try using the .List property. See attached demo.

Reply | Quote

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.!

Reply | Quote

See my answer to this question in the Excel forum.

Reply | Quote

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.

Reply | Quote