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

Bagharmin

Board Regular
Joined
Nov 22, 2010
Messages
168
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Bagharmin

Board Regular
Joined
Nov 22, 2010
Messages
168
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:

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,220
Where does the value LookFor come from? Is it the .Value of one of your controls?
 

Bagharmin

Board Regular
Joined
Nov 22, 2010
Messages
168
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

Board Regular
Joined
Nov 22, 2010
Messages
168
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,990
Messages
5,856,679
Members
431,828
Latest member
kARTIK12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top