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

Bagharmin

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

Board Regular
Joined
Nov 22, 2010
Messages
167
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
22,697
Where does the value LookFor come from? Is it the .Value of one of your controls?
 

Bagharmin

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

Forum statistics

Threads
1,078,466
Messages
5,340,484
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top