RE: Selecting a 2 column listbox item and displaying the result on another userform Textbox...

[chazrab]

Listbox1 on Userform1 has just two cols A = ITem and B = Amt. from Rowsource = Sheet1!A1:B15 = Named range = MYITEMS"
Listbox1 displays this:

Apples           $5.00
Oranges        $4.00
Grapes          $2.00

When say, Apples is selected(highlighted) the values are copied to Textbox1 of Userform2
and are displayed like this:

Apples     $5.00

The value of $5.00 displays in Textbox2 of Userform2;
Oranges is selected next. Textbox1 of Userform2 now displays

Apples   $5.00
Oranges $4.00

Textbox2 of Userform2 now displays $9.00
All of this to simply say:
1 Select an item from a userform Listbox and
2. Display that item with all columns in a Textbox1 on userform2
3 Add the values in COL B each time(cumulate) in a second Textbox2 on userform2.

Sorry for being long and drawn out. Just wanted to explain fully what I'd like to have happen.

Any help would be greatly appreciated
Thanks, cr


Oranges are selected

 

[DanteAmor]

You need something like this:

________________________________________________________________________________________________________
It would be simpler if instead of having a texbox1 you also had a listbox in the userform2. Only you should consider that the content in the textbox1 is not lines, it is a single line separated by a carriage return
________________________________________________________________________________________________________
In a module:

Sub OpenForm()
  UserForm1.Show False
End Sub

_________________________________________________
Code in Userform1:

Private Sub ListBox1_Change()
  Dim i As Long, cad As String, amt As Variant, aux As Variant
  UserForm2.TextBox1 = ""
  UserForm2.TextBox2 = ""
  For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
      aux = Len(Me.ListBox1.List(i, 0)) + Len(Format(Me.ListBox1.List(i, 1), "$#0.00"))
      cad = cad & Me.ListBox1.List(i, 0) & Space(30 - aux) & Format(Me.ListBox1.List(i, 1), "$#0.00") & vbCr
      amt = amt + Val(Me.ListBox1.List(i, 1))
    End If
  Next
  UserForm2.TextBox1 = cad
  UserForm2.TextBox2 = Format(amt, "$#0.00")
End Sub

Private Sub UserForm_Activate()
  ListBox1.MultiSelect = fmMultiSelectMulti
  ListBox1.RowSource = Range("MYITEMS").Address
  ListBox1.ColumnCount = 2
End Sub

Private Sub UserForm_Initialize()
  UserForm2.Show False
End Sub

________________________________________________________________________________________________________
Code in userform2

Private Sub UserForm_Activate()
  TextBox1.MultiLine = True
  TextBox1.Font.Name = "Courier"
  TextBox1.Font.Bold = False
  TextBox2.Font.Name = "Courier"
  TextBox2.Font.Bold = False
End Sub

________________________________________________________________________________________________________
Note: Userforms must be shown with the ShowModal = False property, so this is necessary:
UserForm1.Show False
UserForm2.Show False

For the texts and amounts to be aligned in the textbox1 the font "Courier" is required. There are other fonts, but this is a common one.

 

[chazrab]

You need something like this:

View attachment 3554
________________________________________________________________________________________________________
It would be simpler if instead of having a texbox1 you also had a listbox in the userform2. Only you should consider that the content in the textbox1 is not lines, it is a single line separated by a carriage return
________________________________________________________________________________________________________
In a module:

Sub OpenForm()
  UserForm1.Show False
End Sub

_________________________________________________
Code in Userform1:

Private Sub ListBox1_Change()
  Dim i As Long, cad As String, amt As Variant, aux As Variant
  UserForm2.TextBox1 = ""
  UserForm2.TextBox2 = ""
  For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
      aux = Len(Me.ListBox1.List(i, 0)) + Len(Format(Me.ListBox1.List(i, 1), "$#0.00"))
      cad = cad & Me.ListBox1.List(i, 0) & Space(30 - aux) & Format(Me.ListBox1.List(i, 1), "$#0.00") & vbCr
      amt = amt + Val(Me.ListBox1.List(i, 1))
    End If
  Next
  UserForm2.TextBox1 = cad
  UserForm2.TextBox2 = Format(amt, "$#0.00")
End Sub

Private Sub UserForm_Activate()
  ListBox1.MultiSelect = fmMultiSelectMulti
  ListBox1.RowSource = Range("MYITEMS").Address
  ListBox1.ColumnCount = 2
End Sub

Private Sub UserForm_Initialize()
  UserForm2.Show False
End Sub

________________________________________________________________________________________________________
Code in userform2

Private Sub UserForm_Activate()
  TextBox1.MultiLine = True
  TextBox1.Font.Name = "Courier"
  TextBox1.Font.Bold = False
  TextBox2.Font.Name = "Courier"
  TextBox2.Font.Bold = False
End Sub

________________________________________________________________________________________________________
Note: Userforms must be shown with the ShowModal = False property, so this is necessary:
UserForm1.Show False
UserForm2.Show False

For the texts and amounts to be aligned in the textbox1 the font "Courier" is required. There are other fonts, but this is a common one.

Hi Dante,
You example with the userform images is exactly what I want.
I’m having some issues and I can’t seem to figure out why your code is not acting the way it should when Userfom1 is opened from a button on a sheet already with data in A2:C15. Here’s what’s happening:

If Userform1 is opened from a button on a sheet named BUDGET in this case, which already has data
In rows A1:C15, Listbox1 populates with the data on that BUDGET sheet in rows A2:C15

If I place the button that opens userform1 on a blank worksheet, Listbox1 has no data
If I copy the named range data for Rowsource to A2:C15 of the blank sheet Listbox1 populates with that data and all of the code works perfectly.

Net net: it’s as if Listbox1 keeps wanting to use the data that the button Userform1 is opened from is on and not the
named range data

Why does the code wat to populate Listbox1 with the data on a sheet that Userform1 is opened from rather than the data from the Named Range ?

Listbox1 should still populate with the Named Range Data should it not, regardless of which sheet Userform1 is opened from, whether data is on that sheet or not ? Confusing to me.

I know this has to be something very simple.

Could you please help to solve this ? Thanks again, Great example and code.

 

[DanteAmor]

You need the name of the sheet.
Change this line to this:
ListBox1.RowSource = Range("MYITEMS").Address

By this:
ListBox1.RowSource = "'" & Range("MYITEMS").Parent.Name & "'!" & Range("MYITEMS").Address

 

[chazrab]

Listbox1 now populates correctly. However,
1 "invalid procedure call or argument error at this line:

  cad = cad & Me.ListBox1.List(i, 1) & Space(30 - aux) & Format(Me.ListBox1.List(i, 1), "$#0.00") & vbCr

2. Textbox2 on Userform2 is not displaying and cumulating any amounts. The only thing that appears in Textbox2
when items are selected is $0.00. No values are added to each other

 

[DanteAmor]

Original line
cad = cad & Me.ListBox1.List(i, 0) & Space(30 - aux) & Format(Me.ListBox1.List(i, 1), "$#0.00") & vbCr

Line modified by you
cad = cad & Me.ListBox1.List(i, 1) & Space(30 - aux) & Format(Me.ListBox1.List(i, 1), "$#0.00") & vbCr

If you modify the code, I can't make adjustments, since I can't see what you changed.
____________________________________________________________________________________________________________

I attached my test file so you can see how it works.

Dropbox - File Deleted - Simplify your life

www.dropbox.com