Listbox value updated with multi select on merged cell

  • Dear team,


    I am having a listbox on userform. I want to multiple name should select on the list box and it should updated on merged cell.


    For that i am using below code

    Code
    1. With ws
    2. LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
    3. .Range(.Cells(LstRw + 1, 7), .Cells(LstRw + 10, 7)).Merge
    4. .Cells(LstRw + 1, 7) = ListBox1.Value
    5. .Cells(LstRw + 1, 7).VerticalAlignment = xlCenter
    6. .Cells(LstRw + 1, 7).HorizontalAlignment = xlCenter
    7. End With


    But it is not updating. Only cells are merging. but values not updating


    I am attaching my file here.


    Kindly anyone correct my code and update me

  • Hello,


    Just had a look at your file ...


    My guess is you are trying to solve the problem in UserForm3 ...


    There is a ' Select Trainee Names ' field next to ListBox1 - MultiSelect


    Would recommend OzGrid perfect explanation :


    https://www.ozgrid.com/VBA/multi-select-listbox.htm


    Hope this will help

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

  • Dear Carim,


    You are correct. i am trying to solve on userform3 only.


    I had checked the code which you have sent on the link. It is updating listbox values on my range. I am selecting 3 names on the list box. But it is updating only one name on my range. i had checked these with and without merged my range.


    All the three names are updating on same cell.


    I think i am doing mistake on selecting and updating last row.


    Mu updated code is

    Can you please check the code and correct

  • Hello,


    You could test following set of instructions


    Code
    1. For lItem = 0 To ListBox1.ListCount - 1
    2. If ListBox1.Selected(lItem) = True Then
    3. With ws
    4. LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
    5. .Cells(LstRw + i, 7) = ListBox1.List(lItem)
    6. .Cells(LstRw + i, 7).VerticalAlignment = xlCenter
    7. .Cells(LstRw + i, 7).HorizontalAlignment = xlCenter
    8. End With
    9. End If
    10. Next lItem

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

  • I'm sure that it has been pointed out to you the problems with merged cells.


    I suggest that you give meaningful names to your userforms and controls. I take it you are referring to UserForm3.


    Your code is only entering the current value into the cell, not multi selections.


    Have a look at this example. The Post Selection button will enter the selected items to F1. It does not merge cells but wraps the text.



    Sorry, I must have missed the reply button because this didn't upload yesterday

  • Dear Carim,


    Now only I checked your code . But there is no output as my requirement. Then i changed the code


    Below is new code


    It is working as per my condition with merged the cell

  • Dear Carim,


    The above code i am merging 10 rows by default because i am filtering and copying 10 values from Sheet"Topics". Some times my filter is less than 10. So it is copying the available datas and updating to Sheet "Schedule".


    Example: My filtered values is only 4. It is copying the 4 values only and updating on Sheet "Schedule". But it is merging 10 cells. I want it should merge only the 4 cells. Not merge for blank rows.


    Can you please kindly help me to correct my code

  • Glad you could fix your problem thanks to the revised macro


    Already indicated to you ...that Merged Cells should just be forbidden ...:cursing::evil:

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

  • You are correct. But without merging cells it is not looking good and I want merged cells only.


    Very sorry cannot help you any further ...


    If you have ' cosmetics objectives ' .... and absolutely need to work with merged cells ...


    May a two-step solution could help :


    1. Perform all the necessary actions in a separate column


    2. Then add a specific macro which will create your merged cells

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