Desperate: Updating Listbox Contents

[Jenn68]

Hi folks,

I've been struggling to find a solution to one of the last hurdles of my application. I've lost a lot of time guessing at code to accomplish what I think needs to be done to allow me to proceed.

First, the relevant component of the userform. On my userform ("edit_wo"), I provide the user a listbox of data gathered by a second worksheet ("CONTROL_1"). The rows of this source data are referred to as individual records, each uniquely identified by a record number in column A. The listbox displays select columns from the source database.

First ... consider these two userform modules:

Please Login or Register  to view this content.

The code above is what I use to populate the 7 column listbox. Since the number of rows in the source data ("CONTROL_1") is dynamic based on the dataset being used, the listbox data is compiled based on the number of rows in Column A (which is the unique record number column). This populates the listbox quite nicely with all the records in the source worksheet.

Having the records now available in thelistbox, the user can access the records to make review each one and make any ammendments necessary. As reviews and changes are made, the user clicks a [APPROVE] button which process the changes into the database, and sets a flag field (column DZ) to a value of 1 ... indicating the record has been reviewed. A default of "" is used to indicate a record has not been reviewed.

When the user is confident that the all records have been reviewed, they click a [SUBMIT] button which does some error checking and further data processing before eventually saving the dataset. Part of the error checking must include ensuring all the records have been reviewed. I use the flag value in column DZ for that. I count the number of unreviewed records (DZ = ""), report that number, and proceed with compiling a list of those record numbers (column A) in another worksheet ("Reference"). The code below does this.

Please Login or Register  to view this content.

What I am having great difficulty doing, is updating the listbox now, to only display those compiled records that remain without review (DZ=1).

I tried two options (Opt1, Opt2 in blue). Neither worked. In both cases the lstbox continued to display all rows regardless of the value in DZ. The likely culprit is this line in the "AddMultipleColumn" module:

Please Login or Register  to view this content.

Since I did not write these two modules, I assume this line ignores any filters and defaults to all values in column A.

Please ... someone help me figure out a way to ammend this code to populate only those records in DZ that are "" (empty) rather than just all the records in A alone.
If this is too complex, perhaps someone can advise of a resource for quick paid support.

Jenn

[watersev]

hi Jenn, welcome to Excel Forum, try to change this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Remarks:
1. When using SpecialCells you need to consider its limitations and erroring out if no cell match. The usual way is to assign specialcells to a range object and check if it is not nothing.
2. After using Autofilter it usually helps to check if there is any data to work with afterwards. The way to check if the last data cell row of the column is more then 1 (header row)

[Jenn68]

Fantastic watersev!!

Thank you sooooo much. I appreciate your help.

Jenn