Overview: Before I get into the problem, I'd like to quick explain how this routine works.
(1) When you open the attached file you'll see a command button on the "List" tab that says "Userform" . When you press the button a userform populates.
(2) If you type a couple of letters in the search bar, the userform queries the list of data in the the "data" tab and only lists the items where a match was found.
(3) The user than selects the items desired from the list generated and hits the add button (+). The items then list under B10.
While this system works wonderfully, I'd like to take it a couple steps further...
Problem 1: The user must have the option to select where to list the items. For example, if the user wants to list everything starting at row 21 in column B, that's where the list needs to generate. Or if it is row 17 the list must generate there. While the list must always be confined to column B, the user must have the choice to select which row to start the list at.
Tentative Solution1: I studied it out, and I think the best way forward would be to generate an inputbox that asks "where to place the selected items?". The user than clicks the desired cell and the items generate underneath, always in column B. I was able to make this work in a module, but can't seem to figure it out using the userform. Any help would be appreciated.
Problem2 (Second-order effect): Although I must give the user a choice where to place the list, I know that at times the user may choose a location where there isn't enough space. While I could simply do something like .End(xlUp).Row, I don't want to do that because then the information will just become scattered. A second way I was playing around with is to automatically generate new rows, but the spreadsheet where I will integrate this on to must maintain a specific format and I'd rather not play around with adding new rows and matching formats.
Tentative Solution2: I think if there was a way just to input an error message that let's the user know there isn't enough space available, that would suffice. The user can then go and add the rows where he needs to and retry the userform until he/she gets it right.
I know I'm asking a lot. But I'm struggling on this one. Any help would be appreciated. Thank you.
Bookmarks