Hi folks, This one's a real doosie!
:-(
Although some of me previous questions have been basic learning, this really
has me stumped. I can do it, but my way will take a long...LONG...time!
So.....basically I have two workbooks. Book1 is my user interface, and Book2
is just a data storage workbook with a single worksheet.
In Book2 I have a column of names for which I have created a dynamic named
range for Column A. I have specified for this range that it will allow up to
3000 rows of data (Rows 2 to 3001 in Column A). The columns B to AH have
other data corresponding to each name in Cloumn A....ok...so...
In Book1 I have created a list box which is linked to the dynamic named
range in Book2. Thus, my list box will always display only whatever names are
included in those 3000 rows of column A (Maybe 20names out of a max of 3000
initially), and none of the blank rows further down........are you still with
me?....The list box in Book1 has the standard cell link to display the row
number of the name that has been selected when the user clicks on one in the
list, (2,3,4,5.....2999,3000,3001).
Also in Book 1, beside and around the list box I have a variety of cells,
each of which displays the data corresponding to the employee, taken from the
other columns in Book2. (In Book2 Cell A2 will be a name, Cell B2 telephone
number, Cell C2 age etc to AH). There is a Botton beside the list box which
says "Display Employee Data". When the user selects a name from the list and
clicks the button all the other data for that name from Book2 will be drawn
over using the Macro.
At present I think I have to write this buttons Macro to use SELECT CASE.
But I'll have 3000 CASE value lines of code to manually write. Surely there
has to be a better way than typing in 3000 lines of VBA code for this?
.....can I use a few different SUB/END SUB sections, and denote the cell link
number as X or something in the code to draw the data across, and in my
button macro only denote what X is equal to, VBA automatically adjusting to
the value of X ?
Now for the doosie......I actually have 72 data storage workbooks like
Book2, each with dymanic ranges from 1000 to 5000 row values, so I'll be
writing until next year doing it with Select Case!!
HELP.....PLEasseeeeee!!!!!