Trouble extracting values from user form list box and text box onto sheet

nsterin

New Member
Joined
Jun 8, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello all! I appreciate any time and help anyone can give me. I am trying to figure out how to extract data selected on a multi-column list box and a textbox from a user form into the next available rows of a sheet, this is for entering takeout orders on a sort of log. As you can see the list box is populated from Sheet1, Ctrl+y opens up my user form, in there I have a multi-column list box with the items (column 1) and price for each (column 2), as well as a text box in which you type in the address for where those items must be sent out to.

So far, I am able to select multiple items from the list and copy them along with the text box value next to them onto Sheet2 (address in column a and items in column b), but i can't seem to find a way of copying the price for each item onto column c.

Hope I am clear enough with regards to my issue!

Best regards,

Nick S

Private Sub CommandButton1_Click()
Dim NextBlankRow As Long
Dim TargetRange As Range
Dim ListBoxItem As Long
Dim SelectedItemsArray As Variant
Dim ArrayElementCounter As Long

ArrayElementCounter = 0

With Me.ListBox1
ReDim SelectedItemsArray(0 To .ListCount - 1)
For ListBoxItem = 0 To .ListCount - 1
If .Selected(ListBoxItem) Then
SelectedItemsArray(ArrayElementCounter) = .List(ListBoxItem)
ArrayElementCounter = ArrayElementCounter + 1
End If
Next ListBoxItem
End With

ReDim Preserve SelectedItemsArray(0 To ArrayElementCounter - 1)

With ThisWorkbook.Sheets("Sheet2")
NextBlankRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Set TargetRange = .Range("A" & NextBlankRow & ":A" & NextBlankRow + UBound(SelectedItemsArray))
End With

Dim TargetCell As Range

ArrayElementCounter = 0
For Each TargetCell In TargetRange
TargetCell.Value = Me.TextBox1.Value
TargetCell.Offset(0, 1).Value = SelectedItemsArray(ArrayElementCounter)
TargetCell.Offset(0, 2).Value = UserForm1.ListBox1.List(ListBox1.ListIndex, 2) 'HERE IS WHERE I CRASH AND BURN
ArrayElementCounter = ArrayElementCounter + 1
Next TargetCell

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,765
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.
Textbox in column A
Column1 of listbox in column B
Column2 of listbox in column C

Ttry this:

VBA Code:
Private Sub CommandButton1_Click()
Dim i As Long
With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2).Resize(1, 3).Value = Array(TextBox1.Value, .List(i, 0), .List(i, 1))
  Next
End With
End Sub
 

Forum statistics

Threads
1,143,907
Messages
5,721,439
Members
422,362
Latest member
elliotpat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top