Some UserForm TextBoxes won't update when their values are changed

[Bagharmin]

I have a userform with 6 listboxes (you can select one option in each listbox) on the top half of the form. The bottom half of the userform has several textboxes that are supposed to change as you make selections in the listboxes. My problem is that the first time you make a selection in some of the listboxes (some of them, not all), the textbox values won't change. They will change on the second selection you make, but not the first. The only time they change on the first selection (not including the default they're set at when the form activates) is when you are stepping through the code, line by line.

It's just two of the six listboxes that are having this issue; the other four work fine. Any clue why this is happening?

Thanks.

 

[Bagharmin]

I’ve made some progress, in that I found out where things are going wrong (just not why).

I have a formula (in column U) that identifies records that match the selections made in the userform listboxes. The formula gives 1 for the first record that matches, 2 for the 2nd and so on, giving 0’s when the record doesn’t match. When my userform activates (and no selections have been made) the formula just sequentially numbers each record (the first record is in row 2). My problem is occurring when I try to find the first matching record:

With Worksheets(“Raw Data”)
	LastRow = .UsedRange.Rows.Count
	Set AllRecords = .Range(“U2:U” & LastRow)
CurrRow = Application.WorksheetFunction.Match(LookFor, AllRecords, 0) + 1
End With

“LookFor” is the number of the record I’m looking for (1 in this case).

For some reason, when I click on one of the two offending listboxes for the first time, even though the formula in column U correctly identifies the matching records, the value of CurrRow is 2, as if the record in row 2 was a matching record (which it isn’t). In other words, it's calculating CurrRow as if the initial values in column U were still there, even though they have been changed. If I make a second selection in the same listbox, or step through the code line by line, CurrRow is calculated correctly. If I make a selection in any of the other 4 listboxes, this problem does not occur.

Does anyone know why this happens? I even tried adding a “DoEvents” statement, just in case it was calculating CurrRow before the formula in column U was able to recalculate based on the selection I made, but it didn’t work.

 

[mikerickson]

Where does the value LookFor come from? Is it the .Value of one of your controls?

 

[Bagharmin]

For each of the listboxes on the form, I have a short ListBox_Click sub which, eventually, calls the main sub where the offending bit of code is. Before any one of them calls the main sub, it sets the value of cell Q2 on another sheet to 1. Before anything else changes the value of that cell, the main sub assigns its value to "LookFor". The thought behind it is that, now that the records that match my selections have changed, I want to start again and identify the first matching records. I added the ability to view each record on the userform one at a time by clicking a button. Clicking that button increases the value in cell Q2 by 1, then calls the main sub, which assigns that new cell value to "LookFor".

In looking at the sheet where the records are, my formula is correctly identifying which record should be the first one, but the macro isn't catching it.

 

[Bagharmin]

And, to top it all off, I just re-sized a couple of the listboxes on the userform. Now the 2 that didn't function, function well, while two others now don't work. Once I noticed that, I experimented with resizing the listboxes. Each time I re-sized one that hadn't worked, it started working properly, but some other listbox wouldn't. Be honest, have I just completely lost my mind?