+ Reply to Thread
Results 1 to 4 of 4

Userform: Listing data under activecell using an inputbox

  1. #1
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    150

    Smile Userform: Listing data under activecell using an inputbox

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Userform: Listing data under activecell using an inputbox

    For the first part try this.
    Please Login or Register  to view this content.

    For the second part you probably want to populate an array with all the items the user has selected, have them select the destination and then check if there's room to put all the items where they've selected.
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Userform: Listing data under activecell using an inputbox

    Here's is something you can work with.
    You may have to fiddle with the row counts, but you will get the idea....

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    11-21-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    150

    Re: Userform: Listing data under activecell using an inputbox

    davesexcel,

    That's pretty clever. It looks like before inputting the data selected you count the rows required. The routine then looks at the number of empty cells ("") and if there is enough room it inserts the data otherwise the MsgBox "Not Enough Room!" displays. Brilliant! That's exactly what I hoped for it to do! Thanks for everything!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pass activecell value from worksheet to userform
    By BobZZ in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2017, 06:37 PM
  2. insert date to activecell by userform calendar
    By a.hudrea in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-24-2014, 08:54 AM
  3. [SOLVED] Passing activecell value to Inputbox of another workbook and run macro there
    By JohnnyBGood in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-01-2012, 09:54 AM
  4. show changing activecell value in floating userform
    By tenk283 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-26-2010, 02:25 AM
  5. error in activecell.offset when i open userform
    By ncaravela in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-07-2010, 11:56 AM
  6. Userform Text Based on ActiveCell
    By JimmyA in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2009, 08:41 AM
  7. Focus activecell after display of modeless userform
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-08-2009, 08:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1