Looking for advice and help.
I have a user form that can enter, search, edit and delete information that I need to expand on.
Currently all the information is stored in 1 sheet, "Data". Which is the source and copy location for a filter for the "master data" (if you will).
For example, if I wanted to search for "Doe" in my user form, I would select the column header from a combobox in the userform, which would populate cell AA1, and from a textbox in the userform, "doe" would be populated in cell AA2, which would then search, filter and copy the data from Row 2, A:V to Row 2 AC:AX, and then display that information in listbox in the userform. I can then double click the data in the listbox which populates associated textbox's, which I can edit and save the new information.
This is all well and good for the first 7 columns A:G. The problem is, there are times I need to search by either, year, color, make, model or plate#. Since this information is in multiple columns, and I need it to be displayed in the listbox as single line items, I need to copy and keep up to date, the same information in a 2nd sheet.
<tbody>
</tbody>
Because I'm having problems being able to search multiple columns and display this information in the UserForm, I need to basically copy this information and condense it so it will display properly in the userform.
<tbody>
</tbody>
I see there being 2 options for this.
1. When the data is entered, I could write into the VBA to enter it into the 2 different locations. If I do that, then I would also need to write into the sub's to also edit or delete in the 2 locations.
2. Write a single sub, that copies the current data from sheet 1 (data) to the 2nd location sheet 2 (data2). But if I do a copy and paste code, won't it just keep adding the data to it? Or do I need to do some sort of "clear" to remove the data in the 2nd location? Or a filter?
With option 1, I think I can figure out how to write all of that, just going to be a bit time consuming and I think susceptible to errors. Because even though it's supposed to be edited through the userform, I can see someone going directly to the worksheet and editing the data there.
With option 2, I have no idea how that code would even look.
Please advise....and thank you for your help.
I have a user form that can enter, search, edit and delete information that I need to expand on.
Currently all the information is stored in 1 sheet, "Data". Which is the source and copy location for a filter for the "master data" (if you will).
For example, if I wanted to search for "Doe" in my user form, I would select the column header from a combobox in the userform, which would populate cell AA1, and from a textbox in the userform, "doe" would be populated in cell AA2, which would then search, filter and copy the data from Row 2, A:V to Row 2 AC:AX, and then display that information in listbox in the userform. I can then double click the data in the listbox which populates associated textbox's, which I can edit and save the new information.
This is all well and good for the first 7 columns A:G. The problem is, there are times I need to search by either, year, color, make, model or plate#. Since this information is in multiple columns, and I need it to be displayed in the listbox as single line items, I need to copy and keep up to date, the same information in a 2nd sheet.
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Lot | SPACE # | Sched | Last Name | First Name | ID NUMBER | Grade | V1 Year | V1 Color | V1 Make | V1 Model | V1 Lic. PLATE # | V2 Year | V2 Color | V2 Make | V2 Model | V2 Lic. PLATE # | V3 Year | V3 Color | V3 Make | V3 Model | V3 Lic. PLATE # |
2 | Gold | 306 | EVIT | Doe | John | 111277 | 12 | 2004 | Gold | Toyota | Highlander | AAA1111 | 2004 | Grey | Toyota | Camry | ABC3242 | 2008 | Black | Honda | Accord | OUI2309 |
3 | Gold | 309 | ER | Smith | Sam | 122448 | 12 | 2000 | Blue | Ford | Ranger | ASD7987 | ||||||||||
4 | Gold | 310 | ER | Bowers | Hedwig | 364268 | 12 | 2005 | Gold | Acura | TL | GVS8937 | 2002 | White | Ford | F150 | WJH6432 |
<tbody>
</tbody>
Data
Because I'm having problems being able to search multiple columns and display this information in the UserForm, I need to basically copy this information and condense it so it will display properly in the userform.
A | B | C | D | E | F | G | H | I | J | K | L | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Lot | SPACE # | Sched | Last Name | First Name | ID NUMBER | Grade | Year | Color | Make | Model | Lic. PLATE # |
2 | Gold | 306 | EVIT | Doe | John | 111277 | 12 | 2004 | Gold | Toyota | Highlander | AAA1111 |
3 | Gold | 306 | EVIT | Doe | John | 111277 | 12 | 2004 | Grey | Toyota | Camry | ABC3242 |
4 | Gold | 306 | EVIT | Doe | John | 111277 | 12 | 2008 | Black | Honda | Accord | OUI2309 |
5 | Gold | 309 | ER | Smith | Sam | 122448 | 12 | 2000 | Blue | Ford | Ranger | ASD7987 |
6 | Gold | 310 | ER | Bowers | Hedwig | 364268 | 12 | 2005 | Gold | Acura | TL | GVS8937 |
7 | Gold | 310 | ER | Bowers | Hedwig | 362574 | 12 | 2002 | White | Ford | F150 | WJH6432 |
<tbody>
</tbody>
Data2
I see there being 2 options for this.
1. When the data is entered, I could write into the VBA to enter it into the 2 different locations. If I do that, then I would also need to write into the sub's to also edit or delete in the 2 locations.
2. Write a single sub, that copies the current data from sheet 1 (data) to the 2nd location sheet 2 (data2). But if I do a copy and paste code, won't it just keep adding the data to it? Or do I need to do some sort of "clear" to remove the data in the 2nd location? Or a filter?
With option 1, I think I can figure out how to write all of that, just going to be a bit time consuming and I think susceptible to errors. Because even though it's supposed to be edited through the userform, I can see someone going directly to the worksheet and editing the data there.
With option 2, I have no idea how that code would even look.
Please advise....and thank you for your help.