Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't added two columns from one listbox to two columns in another
Hello,
I have one multi-select two column listbox on a userform. I have a button that will take the selection from the mult-select listbox that has two columns to another listbox that also has two columns. Here is my code: Dim itemIndex As Integer Dim myVar As Variant With frmEmployeeMaintenance .lstClubsAssigned.RowSource = "" 'second listbox If .lstClubs.ListIndex = -1 Then 'first listbox Exit Sub End If For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.Column(0, itemIndex) & ";" & .lstClubs.Column(1, itemIndex) End If Next itemIndex End With I found this code on a help forum, but it is not working for me. It only adds a concatenated string to the first column. How do I add values to both columns? Thanks, Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't added two columns from one listbox to two columns in another
Try...
For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.list(itemindex, 0) .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) End If Next itemIndex End With (Untested, uncompiled. Watch for typos.) ps. If this code is inside the userform module, I'd change this: With frmEmployeeMaintenance to With Me Me is the object that owns the code--in this case that userform. Webtechie wrote: Hello, I have one multi-select two column listbox on a userform. I have a button that will take the selection from the mult-select listbox that has two columns to another listbox that also has two columns. Here is my code: Dim itemIndex As Integer Dim myVar As Variant With frmEmployeeMaintenance .lstClubsAssigned.RowSource = "" 'second listbox If .lstClubs.ListIndex = -1 Then 'first listbox Exit Sub End If For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.Column(0, itemIndex) & ";" & .lstClubs.Column(1, itemIndex) End If Next itemIndex End With I found this code on a help forum, but it is not working for me. It only adds a concatenated string to the first column. How do I add values to both columns? Thanks, Tony -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't added two columns from one listbox to two columns in ano
Dave,
This line: .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) is throwing an error 13 - Type mismatch. What is that about? It doesn't like the .lstclubsassigned(.lstclubassigned.listcount - 1, 1). I've added ..lstClubsAssigned.ColumnCount = 2 just to be sure it knows there are two columns. Same error. Thanks, Tony "Dave Peterson" wrote: Try... For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.list(itemindex, 0) .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) End If Next itemIndex End With (Untested, uncompiled. Watch for typos.) ps. If this code is inside the userform module, I'd change this: With frmEmployeeMaintenance to With Me Me is the object that owns the code--in this case that userform. Webtechie wrote: Hello, I have one multi-select two column listbox on a userform. I have a button that will take the selection from the mult-select listbox that has two columns to another listbox that also has two columns. Here is my code: Dim itemIndex As Integer Dim myVar As Variant With frmEmployeeMaintenance .lstClubsAssigned.RowSource = "" 'second listbox If .lstClubs.ListIndex = -1 Then 'first listbox Exit Sub End If For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.Column(0, itemIndex) & ";" & .lstClubs.Column(1, itemIndex) End If Next itemIndex End With I found this code on a help forum, but it is not working for me. It only adds a concatenated string to the first column. How do I add values to both columns? Thanks, Tony -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't added two columns from one listbox to two columns in ano
I don't think it's the code.
I think it may be one of the settings you've made when designing the userform. I created (and tested!) a small userform with 2 listboxes and a commandbutton. This was the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim ItemIndex As Long Me.lstClubsAssigned.Clear '??????? With Me.lstClubs For ItemIndex = .ListCount - 1 To 0 Step -1 If .Selected(ItemIndex) = True Then Me.lstClubsAssigned.AddItem .List(ItemIndex, 0) Me.lstClubsAssigned.List(Me.lstClubsAssigned.ListC ount - 1, 1) _ = .List(ItemIndex, 1) End If Next ItemIndex End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.lstClubs .RowSource = "" .ColumnCount = myRng.Columns.Count .MultiSelect = fmMultiSelectMulti .List = myRng.Value .ColumnWidths = "50;50" End With With Me.lstClubsAssigned .RowSource = "" .ColumnCount = myRng.Columns.Count .MultiSelect = fmMultiSelectMulti .ColumnWidths = "50;50" End With End Sub ======== One more thing to check. Make sure you don't have the .rowsource assigned to the second listbox--either in code or in the properties window. That doesn't play well with .additem. But I'm not sure that would cause the error you're seeing. Webtechie wrote: Dave, This line: .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) is throwing an error 13 - Type mismatch. What is that about? It doesn't like the .lstclubsassigned(.lstclubassigned.listcount - 1, 1). I've added .lstClubsAssigned.ColumnCount = 2 just to be sure it knows there are two columns. Same error. Thanks, Tony "Dave Peterson" wrote: Try... For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.list(itemindex, 0) .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) End If Next itemIndex End With (Untested, uncompiled. Watch for typos.) ps. If this code is inside the userform module, I'd change this: With frmEmployeeMaintenance to With Me Me is the object that owns the code--in this case that userform. Webtechie wrote: Hello, I have one multi-select two column listbox on a userform. I have a button that will take the selection from the mult-select listbox that has two columns to another listbox that also has two columns. Here is my code: Dim itemIndex As Integer Dim myVar As Variant With frmEmployeeMaintenance .lstClubsAssigned.RowSource = "" 'second listbox If .lstClubs.ListIndex = -1 Then 'first listbox Exit Sub End If For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.Column(0, itemIndex) & ";" & .lstClubs.Column(1, itemIndex) End If Next itemIndex End With I found this code on a help forum, but it is not working for me. It only adds a concatenated string to the first column. How do I add values to both columns? Thanks, Tony -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't added two columns from one listbox to two columns in ano
Dave,
You were right. I had another procedure that was changing the properties of the listbox. I had forgotten about that code. I made some adjustments and everything is working fine now. Thanks for pointing me in the right direction! Tony "Dave Peterson" wrote: I don't think it's the code. I think it may be one of the settings you've made when designing the userform. I created (and tested!) a small userform with 2 listboxes and a commandbutton. This was the code behind the userform: Option Explicit Private Sub CommandButton1_Click() Dim ItemIndex As Long Me.lstClubsAssigned.Clear '??????? With Me.lstClubs For ItemIndex = .ListCount - 1 To 0 Step -1 If .Selected(ItemIndex) = True Then Me.lstClubsAssigned.AddItem .List(ItemIndex, 0) Me.lstClubsAssigned.List(Me.lstClubsAssigned.ListC ount - 1, 1) _ = .List(ItemIndex, 1) End If Next ItemIndex End With End Sub Private Sub UserForm_Initialize() Dim myRng As Range With Worksheets("Sheet1") Set myRng = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With With Me.lstClubs .RowSource = "" .ColumnCount = myRng.Columns.Count .MultiSelect = fmMultiSelectMulti .List = myRng.Value .ColumnWidths = "50;50" End With With Me.lstClubsAssigned .RowSource = "" .ColumnCount = myRng.Columns.Count .MultiSelect = fmMultiSelectMulti .ColumnWidths = "50;50" End With End Sub ======== One more thing to check. Make sure you don't have the .rowsource assigned to the second listbox--either in code or in the properties window. That doesn't play well with .additem. But I'm not sure that would cause the error you're seeing. Webtechie wrote: Dave, This line: .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) is throwing an error 13 - Type mismatch. What is that about? It doesn't like the .lstclubsassigned(.lstclubassigned.listcount - 1, 1). I've added .lstClubsAssigned.ColumnCount = 2 just to be sure it knows there are two columns. Same error. Thanks, Tony "Dave Peterson" wrote: Try... For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.list(itemindex, 0) .lstclubsassigned(.lstclubassigned.listcount - 1, 1) _ = .lstclubs.list(itemindex, 1) End If Next itemIndex End With (Untested, uncompiled. Watch for typos.) ps. If this code is inside the userform module, I'd change this: With frmEmployeeMaintenance to With Me Me is the object that owns the code--in this case that userform. Webtechie wrote: Hello, I have one multi-select two column listbox on a userform. I have a button that will take the selection from the mult-select listbox that has two columns to another listbox that also has two columns. Here is my code: Dim itemIndex As Integer Dim myVar As Variant With frmEmployeeMaintenance .lstClubsAssigned.RowSource = "" 'second listbox If .lstClubs.ListIndex = -1 Then 'first listbox Exit Sub End If For itemIndex = .lstClubs.ListCount - 1 To 0 Step -1 If .lstClubs.Selected(itemIndex) Then .lstClubsAssigned.AddItem .lstClubs.Column(0, itemIndex) & ";" & .lstClubs.Column(1, itemIndex) End If Next itemIndex End With I found this code on a help forum, but it is not working for me. It only adds a concatenated string to the first column. How do I add values to both columns? Thanks, Tony -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i lock columns but allow for new rows to be added? | Excel Discussion (Misc queries) | |||
Data is added in 1 column instaed of 4 columns (in listbox) | Excel Programming | |||
formula to sum the prior 12 cells regardless of added columns? | Excel Worksheet Functions | |||
Added new columns - Pivot Table not refreshing - why? | Excel Worksheet Functions | |||
Sum of Rows when columns are added | Excel Programming |