Issue with Setting Value of a Single Select Listbox programmatically

[JL_14]

Hi, I've been looking online to see if this problem has been reported else where, and I cannot work it out, and so I've finally registered here after using this forum many times for solutions to previous problems. Anyway:

My issue is this, I have a userform that loads up on double click on a spreadsheet and fills textboxes and listboxes from cells on the row clicked via a loop.

When it comes to filling the value on the userform initialize routine of the listboxes, which are singleselect and Not a multiselect, it does look on the face of it that a value has been selected; the appropriate value for the line clicked is indeed highlighted.

However, when I inspect further the listboxes don't always have a .value when I check with a msgbox or debug.print. In fact there is a pattern: the first time 2 of them have a .value but 1 of them does not. The second time I load it, a different two have. The third time only 1 has a .value, and the fourth a different one again. This then repeats from the fifth time.

Obviously this has confused me. I have placed an if .value = "" then set .value again in within the userform.activate and this also does nothing, although it runs through the loop as I would require but failing to actually assign .value . Within the immediate window I have tried setting the appropriate values but again nothing. No error or anything. For the listboxes that do have a .value correctly set I can edit it in the immediate window however.

In case anyone asks for the code, this is within the userform.intialize of the userform. Each control is named Col_ and then the column number for ease of rotating through.

for i = 1 to 18
        CName = "Col_" & i
        set MyControl = Controls(CName)
        MyControl.Value = MySht.Cells(Line, i).Value
next i

I use option explicit, so I have already defined i as integer, mycontrol as control, MySht as worksheet, line as integer etc. The code works fine, as said, but sometimes excel is not assigning the .value of the listbox, even though it is highlighted. I have tried doing the multiselect listbox thing where I loop through and change the .selected property as appropriate but again to no available.

So in conclusion, is this a known problem, replicatable problem, or an issue with my workbook or my excel? I use excel 2007 also in case someone asks.

Thank you,

Joe

 

[JL_14]

Created a work around by setting the .tag of the listbox should it fail to pick up the .value and then putting a check later in code. Don't know what causes the issue, but within my trials I decided to make a sheet that recorded a 1 for every successful .value, and a 0 for none. Then I kept double clicking on the same row and opening the userform. Came up with

1 0 0
0 0 1
0 1 1
1 1 0

and repeat. Strange