i need a quotation creating form which must place the filled data to an excel sheet including the multiple selection listbox data

[LUVYOGESH100]

i am finding it difficult to add the multiple selected data of the listbox along with other textbox data in sepereate rows for the same quotation.
kindly help me,i can share the workbook

 

[My Aswer Is This]

So to be clear:
You have a Userform. With a multiselect listbox.

So if you select three items on the listbox and then click a button.
Where on you sheet do you want these three values entered.

Do you want them entered into column A on the active sheet

Like: A1 Then A2 and then A3

Please provide specific details like this.
If not the active sheet then give sheet name.

 

[My Aswer Is This]

This script assigned to a command button on your userform will enter all the values you have selected from a multiselect listbox into column 5 of the active sheet.

See if this is what you want:

Private Sub CommandButton2_Click()
'Modified  10/31/2018  4:33:26 AM  EDT
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, 5).End(xlUp).Row + 1
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            Cells(Lastrow, 5).Value = ListBox1.List(i)
            Lastrow = Lastrow + 1
            ListBox1.Selected(i) = False
        End If
    Next
End Sub

 

[LUVYOGESH100]

This script assigned to a command button on your userform will enter all the values you have selected from a multiselect listbox into column 5 of the active sheet.

See if this is what you want:

Private Sub CommandButton2_Click()
'Modified  10/31/2018  4:33:26 AM  EDT
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, 5).End(xlUp).Row + 1
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            Cells(Lastrow, 5).Value = ListBox1.List(i)
            Lastrow = Lastrow + 1
            ListBox1.Selected(i) = False
        End If
    Next
End Sub

my actual code
Dim emptyRow As Long


'Make Sheet2 active
Sheet2.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = qdate.Value
Cells(emptyRow, 3).Value = materialdept.Value




If OptionButton1.Value = True Then
Cells(emptyRow, 4).Value = "INTERNAL"
Else
Cells(emptyRow, 4).Value = "EXTERNAL"
End If
Cells(emptyRow, 5).Value = duration.Value


'this is where i face problem,the multiple selection items must be stored individually for the same quotation in different rows along with other data


Cells(emptyRow, 6).Value = materialcode.Value


Cells(emptyRow, 7).Value = depthead.Value

 

[LUVYOGESH100]

my actual code
dim emptyrow as long


'make sheet2 active
sheet2.activate


'determine emptyrow
emptyrow = worksheetfunction.counta(range("a:a")) + 1


'transfer information
cells(emptyrow, 1).value = nametextbox.value
cells(emptyrow, 2).value = qdate.value
cells(emptyrow, 3).value = materialdept.value




if optionbutton1.value = true then
cells(emptyrow, 4).value = "internal"
else
cells(emptyrow, 4).value = "external"
end if
cells(emptyrow, 5).value = duration.value


'this is where i face problem,the multiple selection items must be stored individually for the same quotation in different rows along with other data


cells(emptyrow, 6).value = materialcode.value


cells(emptyrow, 7).value = depthead.value

the code what you sent works perfectly but adds one extra row at the end??

 

[LUVYOGESH100]

so to be clear:
You have a userform. With a multiselect listbox.

So if you select three items on the listbox and then click a button.
Where on you sheet do you want these three values entered.

Do you want them entered into column a on the active sheet

like: A1 then a2 and then a3

please provide specific details like this.
If not the active sheet then give sheet name.

thanks a ton
great help
the code works fine
i will never forget your help

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

thanks a ton
great help
the code works fine
i will never forget your help