Populate worksheet active range from userform listbox selection

  • Hi,


    I know there is possibly an argument that this could be done by simply using data validation dropdowns but i think this has a lot more ease for the user as it breaks out the names into teams. Is there a way the user can double click a name from the listbox and populate the active cell they have used to trigger the userform from ?


    I have attached a sample workbook where the userform can be launched using the below code, i have looked through the forum's historic posts but cant seem to find anything i can twist to suit.


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Application.Intersect(Target, Range("A8:G11,I8:P11")) Is Nothing Then
    3. Cancel = True
    4. FrmNames.Show
    5. End If
    6. End Sub
  • Hello,


    The very first remark is to TOTALLY avoid MERGED CELLS :thumbdown::cursing: the source of endless problems ...


    The second remark is : Yes, Excel does offer a lot of flexibility... but it should not be used as a vulgar word processor ...


    Attached is your test file


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the macros ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    To the rescue once again, this works perfectly.


    NOTED not to use merged cells going forward, this did seem to be the best move forward to get users to quickly select names as these are dockets that need to be printed off and handed to staff. There wasnt enough space to go through dynamic dropdowns where users can select the team first then the name etc....


    As always, thank you very much for taking your free time out to resolve this issue and help me get my head around these problems and also give advice. Have a great weekend.

  • Glad to hear you could fix your problem :)


    AND that you will AVOID MERGED CELLS in future :love:


    Thanks for your Thanks


    Will mark your thread as resolved ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)