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


Board Regular
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?



Board Regular
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.


Board Regular
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.


Board Regular
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?

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...