Userform listbox row selection fails

[Hilarys]

I have a UserForm which includes two ListBoxes on one page of a MultiPage. The two ListBoxes are identical apart from the ControlSource and the position on the form. In one of the ListBoxes, a mouse click on a populated row highlights the row and writes the bound column data to the ControlSource, i.e. as expected. In the other ListBoxe, a mouse click causes the bound column data to flash in the ControlSource, but it then disappears, and no highlighting occurs, so row selection is impossible. What am I doing wrong?

 

[RoryA]

I suspect that writing to the worksheet is causing a recalculation of the rowsource for the control, which then clears the selection. I recommend not binding the controls directly to ranges for this reason.

 

[Hilarys]

I suspect that writing to the worksheet is causing a recalculation of the rowsource for the control, which then clears the selection. I recommend not binding the controls directly to ranges for this reason.

OK, but there are no formulae that use the ControlSource. The RowSource was set from code. And why does it work as it should for one ListBox but not the other?

 

[RoryA]

What is the rowsource set to?

It's very hard to diagnose things like this without the workbook, but the most likely causes are the ones I mentioned, or code that is being triggered when you select an item in the listbox.

 

[Hilarys]

OK. There's only one workbook involved here, and the two ListBoxes each display a (separate) portion of one worksheet, which is not the worksheet referenced in the ControlSource of either. There is no code associated with the selection of a row (other than the MS code to highlight the row and set the ControlSource value). The workbook in question contains proprietary data, but I'll try to build a beige version that misbehaves in the same way. Thanks for the suggestions.

 

[RoryA]

It's most likely the RowSource that is the issue, not the ControlSource.

 

[Hilarys]

It's most likely the RowSource that is the issue, not the ControlSource.

I haven't got a beige version yet, but I did notice that setting the RowSource uses identical code for the two ListBoxes. What's going on is that a list of data values is being sorted into two based on a criterion entered by the user. The two resulting lists are sorted into alpha order and then displayed in the form. There's one block of code that writes the two lists, then sorts them and then sets the RowSource. Control is then passed back to the user.

 

[RoryA]

Are both rowsources set to static range addresses?

 

[Hilarys]

The code is:

mystring = "Display!CG2:CI" & cmy + 10
msincs.MultiPage1.Pages(4).Controls("ctomsexist").RowSource = mystring
mystring = "Display!CM2:CO" & cmn + 10
msincs.MultiPage1.Pages(4).Controls("ctomscand").RowSource = mystring

where cmy and cmn are both integers. "ctomsexist" and "ctomscand" are the ListBoxes, of course.

 

[RoryA]

I think I'd have to see a representative workbook then. Something must be resetting the listbox, from what you describe.