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.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Application.Intersect(Target, Range("A8:G11,I8:P11")) Is Nothing Then
            Cancel = True
        End If
    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


  • 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:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!