|
Edit Listbox in Userform
I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad |
Edit Listbox in Userform
You can probably use the List property to edit. Check it out in VBA help.
" wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad |
Edit Listbox in Userform
On Jun 24, 6:46 pm, JLGWhiz wrote:
You can probably use the List property to edit. Check it out in VBA help. " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi, I checked the listbox property to Edit, but I could not find some thing to edit. I am new in this field. pls help me how to edit listbox row and columns. Thanks Shahzad |
Edit Listbox in Userform
Hi ,
You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan ------------------------------------------------------------------------------------------- On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Su ----------------------------------------------------------------------------------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad |
Edit Listbox in Userform
Here is all the code you need for updating multicolumn list box
Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ----------------------------------------------------------------------------- Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ----------------------------------------------------------------------------------------------- Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub ------------------------------------------------------------------------------ "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan ------------------------------------------------------------------------------------------- On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ----------------------------------------------------------------------------------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad |
Edit Listbox in Userform
On Jun 25, 10:30 pm, Nayan wrote:
Here is all the code you need for updating multicolumn list box Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ---------------------------------------------------------------------------*-- Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ---------------------------------------------------------------------------*-------------------- Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub ---------------------------------------------------------------------------*--- "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan ---------------------------------------------------------------------------*---------------- On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ---------------------------------------------------------------------------*-------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi, Nayan, Thank you for your support and prompt reply. I tried your code but it is update only userform, not in the worksheet. Can you pls check again when I Edit multicolumn as per your code, then worksheet data should be changed. Waiting for your reply. Thanks and Regards. syed shahzad |
Edit Listbox in Userform
Syed, Are you saying that you populate a list from columns; in excel worksheet and then edit these columns in worksheets using this list box ? If that is the case then You can user range object of worksheet to point to a particular cell. Array value from this code that updates the list box can be passed to the cell reference and that will update list box as well as cell in worksheet. application.worksheets(name of worksheet).range( refer to cell with 'col A' and 'Row i' here).value = sNewValueCol1 application.worksheets(name of worksheet).range( refer to cell with 'col B' and 'Row i' here).value = sNewValueCol2 Hope this helps Nayan " wrote: On Jun 25, 10:30 pm, Nayan wrote: Here is all the code you need for updating multicolumn list box Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ------------------------------------------------------------------------------ Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ------------------------------------------------------------------------------------------------ Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub ------------------------------------------------------------------------------- "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan -------------------------------------------------------------------------------------------- On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ------------------------------------------------------------------------------------------------ " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi, Nayan, Thank you for your support and prompt reply. I tried your code but it is update only userform, not in the worksheet. Can you pls check again when I Edit multicolumn as per your code, then worksheet data should be changed. Waiting for your reply. Thanks and Regards. syed shahzad |
Edit Listbox in Userform
On Jun 26, 9:53 pm, Nayan wrote:
Syed, Are you saying that you populate a list from columns; in excel worksheet and then edit these columns in worksheets using this list box ? If that is the case then You can user range object of worksheet to point to a particular cell. Array value from this code that updates the list box can be passed to the cell reference and that will update list box as well as cell in worksheet. application.worksheets(name of worksheet).range( refer to cell with 'col A' and 'Row i' here).value = sNewValueCol1 application.worksheets(name of worksheet).range( refer to cell with 'col B' and 'Row i' here).value = sNewValueCol2 Hope this helps Nayan " wrote: On Jun 25, 10:30 pm, Nayan wrote: Here is all the code you need for updating multicolumn list box Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ---------------------------------------------------------------------------*--- Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ---------------------------------------------------------------------------*--------------------- Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub ---------------------------------------------------------------------------*---- "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan ---------------------------------------------------------------------------*----------------- On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ---------------------------------------------------------------------------*--------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi, Nayan, Thank you for your support and prompt reply. I tried your code but it is update only userform, not in the worksheet. Can you pls check again when I Edit multicolumn as per your code, then worksheet data should be changed. Waiting for your reply. Thanks and Regards. syed shahzad- Hide quoted text - - Show quoted text - Dear Mr. Nayan, I am very happy to see that you sent me reply within one hour. I appreciated ... Sir, the code you sent, I checked but it is not working in my form. May be I am doing some thing wrong. Actually I have a Worksheet name is DATA, having Inventory Data from A2:M99 and I have a userform1, showing one combobox1 and other textboxes to display the data. I am using combobox to retrive data, by using this code: Option Explicit Dim i As Integer Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(1, i) 'This is column B TextBox2.Value = ComboBox1.Column(2, i) 'This is column C TextBox3.Value = ComboBox1.Column(3, i) 'This is column D TextBox4.Value = ComboBox1.Column(4, i) 'This is column E TextBox5.Value = ComboBox1.Column(5, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(7, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(9, i) 'This is column J TextBox10.Value = ComboBox1.Column(10, i) 'This is column K TextBox11.Value = ComboBox1.Column(11, i) 'This is column L TextBox12.Value = ComboBox1.Column(12, i) 'This is column M End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() ComboBox1.SetFocus ComboBox1.RowSource = "A2:N99" End Sub NOW, the problem is this after retriving my records from the worksheet, I am unable to Edit, if I want to change any record (textbox) how I can Edit my records in userform and the worksheet at the same time. this is my big problem. If you send me the solution, I will be highly appreciated. Very sorry to distrub you again and again. Thanks and regards. Syed Shahzad Zafar Madinah - KSA |
Edit Listbox in Userform
Syed,
Following is all the code you need. Coud not reply earliier due to busy schedule. Put a command button name cmdEdit and a combobox on the form Hope this will solve your issue. Have fun Nayan ------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- Private Sub cmdEdit_Click() Dim iRow As Integer iRow = ComboBox1.ListIndex ComboBox1.RowSource = "" iRow = iRow + 1 With Worksheets("Data") .Cells(iRow, 2) = TextBox1.Value 'This is column B .Cells(iRow, 3) = TextBox2.Value 'This is column C .Cells(iRow, 4) = TextBox3.Value 'This is column D .Cells(iRow, 5) = TextBox4.Value 'This is column E .Cells(iRow, 6) = TextBox5.Value 'This is column F .Cells(iRow, 7) = TextBox6.Value 'This is column G .Cells(iRow, 8) = TextBox7.Value 'This is column H .Cells(iRow, 9) = TextBox8.Value 'This is column iRow .Cells(iRow, 10) = TextBox9.Value 'This is column J .Cells(iRow, 11) = TextBox10.Value 'This is column K .Cells(iRow, 12) = TextBox11.Value 'This is column L .Cells(iRow, 13) = TextBox12.Value 'This is column M End With ComboBox1.RowSource = "B1:M10" End Sub Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(0, i) 'This is column B TextBox2.Value = ComboBox1.Column(1, i) 'This is column C TextBox3.Value = ComboBox1.Column(2, i) 'This is column D TextBox4.Value = ComboBox1.Column(3, i) 'This is column E TextBox5.Value = ComboBox1.Column(4, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(6, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(8, i) 'This is column J TextBox10.Value = ComboBox1.Column(9, i) 'This is column K TextBox11.Value = ComboBox1.Column(10, i) 'This is column L TextBox12.Value = ComboBox1.Column(11, i) 'This is column M End Sub Private Sub UserForm_Activate() ComboBox1.ColumnCount = 12 ComboBox1.ColumnWidths = "30;30;30;30;30;30;30;30;30;30;30;30" ComboBox1.RowSource = "B1:M10" End Sub ------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------- " wrote: On Jun 26, 9:53 pm, Nayan wrote: Syed, Are you saying that you populate a list from columns; in excel worksheet and then edit these columns in worksheets using this list box ? If that is the case then You can user range object of worksheet to point to a particular cell. Array value from this code that updates the list box can be passed to the cell reference and that will update list box as well as cell in worksheet. application.worksheets(name of worksheet).range( refer to cell with 'col A' and 'Row i' here).value = sNewValueCol1 application.worksheets(name of worksheet).range( refer to cell with 'col B' and 'Row i' here).value = sNewValueCol2 Hope this helps Nayan " wrote: On Jun 25, 10:30 pm, Nayan wrote: Here is all the code you need for updating multicolumn list box Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ------------------------------------------------------------------------------- Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ------------------------------------------------------------------------------------------------- Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub -------------------------------------------------------------------------------- "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan --------------------------------------------------------------------------------------------- On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ------------------------------------------------------------------------------------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi, Nayan, Thank you for your support and prompt reply. I tried your code but it is update only userform, not in the worksheet. Can you pls check again when I Edit multicolumn as per your code, then worksheet data should be changed. Waiting for your reply. Thanks and Regards. syed shahzad- Hide quoted text - - Show quoted text - Dear Mr. Nayan, I am very happy to see that you sent me reply within one hour. I appreciated ... Sir, the code you sent, I checked but it is not working in my form. May be I am doing some thing wrong. Actually I have a Worksheet name is DATA, having Inventory Data from A2:M99 and I have a userform1, showing one combobox1 and other textboxes to display the data. I am using combobox to retrive data, by using this code: Option Explicit Dim i As Integer Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(1, i) 'This is column B TextBox2.Value = ComboBox1.Column(2, i) 'This is column C TextBox3.Value = ComboBox1.Column(3, i) 'This is column D TextBox4.Value = ComboBox1.Column(4, i) 'This is column E TextBox5.Value = ComboBox1.Column(5, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(7, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(9, i) 'This is column J TextBox10.Value = ComboBox1.Column(10, i) 'This is column K TextBox11.Value = ComboBox1.Column(11, i) 'This is column L TextBox12.Value = ComboBox1.Column(12, i) 'This is column M End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() ComboBox1.SetFocus ComboBox1.RowSource = "A2:N99" End Sub NOW, the problem is this after retriving my records from the worksheet, I am unable to Edit, if I want to change any record (textbox) how I can Edit my records in userform and the worksheet at the same time. this is my big problem. If you send me the solution, I will be highly appreciated. Very sorry to distrub you again and again. Thanks and regards. Syed Shahzad Zafar Madinah - KSA |
Edit Listbox in Userform
On Jun 29, 7:22 am, Nayan wrote:
Syed, Following is all the code you need. Coud not reply earliier due to busy schedule. Put a command button name cmdEdit and a combobox on the form Hope this will solve your issue. Have fun Nayan ---------------------------------------------------------------------------*----------- ---------------------------------------------------------------------------*----------- Private Sub cmdEdit_Click() Dim iRow As Integer iRow = ComboBox1.ListIndex ComboBox1.RowSource = "" iRow = iRow + 1 With Worksheets("Data") .Cells(iRow, 2) = TextBox1.Value 'This is column B .Cells(iRow, 3) = TextBox2.Value 'This is column C .Cells(iRow, 4) = TextBox3.Value 'This is column D .Cells(iRow, 5) = TextBox4.Value 'This is column E .Cells(iRow, 6) = TextBox5.Value 'This is column F .Cells(iRow, 7) = TextBox6.Value 'This is column G .Cells(iRow, 8) = TextBox7.Value 'This is column H .Cells(iRow, 9) = TextBox8.Value 'This is column iRow .Cells(iRow, 10) = TextBox9.Value 'This is column J .Cells(iRow, 11) = TextBox10.Value 'This is column K .Cells(iRow, 12) = TextBox11.Value 'This is column L .Cells(iRow, 13) = TextBox12.Value 'This is column M End With ComboBox1.RowSource = "B1:M10" End Sub Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(0, i) 'This is column B TextBox2.Value = ComboBox1.Column(1, i) 'This is column C TextBox3.Value = ComboBox1.Column(2, i) 'This is column D TextBox4.Value = ComboBox1.Column(3, i) 'This is column E TextBox5.Value = ComboBox1.Column(4, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(6, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(8, i) 'This is column J TextBox10.Value = ComboBox1.Column(9, i) 'This is column K TextBox11.Value = ComboBox1.Column(10, i) 'This is column L TextBox12.Value = ComboBox1.Column(11, i) 'This is column M End Sub Private Sub UserForm_Activate() ComboBox1.ColumnCount = 12 ComboBox1.ColumnWidths = "30;30;30;30;30;30;30;30;30;30;30;30" ComboBox1.RowSource = "B1:M10" End Sub ---------------------------------------------------------------------------*----------- ---------------------------------------------------------------------------*------------ " wrote: On Jun 26, 9:53 wrote: Syed, Are you saying that you populate a list from columns; in excel worksheet and then edit these columns in worksheets using this list box ? If that is the case then You can user range object of worksheet to point to a particular cell. Array value from this code that updates the list box can be passed to the cell reference and that will update list box as well as cell in worksheet. application.worksheets(name of worksheet).range( refer to cell with 'col A' and 'Row i' here).value = sNewValueCol1 application.worksheets(name of worksheet).range( refer to cell with 'col B' and 'Row i' here).value = sNewValueCol2 Hope this helps Nayan " wrote: On Jun 25, 10:30 wrote: Here is all the code you need for updating multicolumn list box Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ---------------------------------------------------------------------------*---- Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ---------------------------------------------------------------------------*---------------------- Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub ---------------------------------------------------------------------------*----- "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan ---------------------------------------------------------------------------*------------------ On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ---------------------------------------------------------------------------*---------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi,Nayan, Thank you for your support and prompt reply. I tried your code but it is update only userform, not in the worksheet. Can you pls check again when I Edit multicolumn as per your code, then worksheet data should be changed. Waiting for your reply. Thanks and Regards. syed shahzad- Hide quoted text - - Show quoted text - Dear Mr.Nayan, I am very happy to see that you sent me reply within one hour. I appreciated ... Sir, the code you sent, I checked but it is not working in my form. May be I am doing some thing wrong. Actually I have a Worksheet name is DATA, having Inventory Data from A2:M99 and I have a userform1, showing one combobox1 and other textboxes to display the data. I am using combobox to retrive data, by using this code: Option Explicit Dim i As Integer Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(1, i) 'This is column B TextBox2.Value = ComboBox1.Column(2, i) 'This is column C TextBox3.Value = ComboBox1.Column(3, i) 'This is column D TextBox4.Value = ComboBox1.Column(4, i) 'This is column E TextBox5.Value = ComboBox1.Column(5, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(7, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(9, i) 'This is column J TextBox10.Value = ComboBox1.Column(10, i) 'This is column K TextBox11.Value = ComboBox1.Column(11, i) 'This is column L TextBox12.Value = ComboBox1.Column(12, i) 'This is column M End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() ComboBox1.SetFocus ComboBox1.RowSource = "A2:N99" End Sub NOW, the problem is this after retriving my records from the worksheet, I am unable to Edit, if I want to change any record (textbox) how I can Edit my records in userform and the worksheet at the same time. this is my big problem. If you send me the solution, I will be highly appreciated. Very sorry to distrub you again and again. Thanks and regards. Syed Shahzad Zafar Madinah - KSA- Hide quoted text - - Show quoted text - Dear Nayan, Thak you for sending me this solution, it is working fine, but when I change some thing in userform it is not replacing the data in worksheet., I want to change the data on the worksheet from the userform, I am still unale to change the records from Userform. pls help me. Regards. Shahzad |
Edit Listbox in Userform
On Jul 1, 9:43 am, wrote:
On Jun 29, 7:22 am, Nayan wrote: Syed, Following is all the code you need. Coud not reply earliier due to busy schedule. Put a command button name cmdEdit and a combobox on the form Hope this will solve your issue. Have fun Nayan ---------------------------------------------------------------------------**----------- ---------------------------------------------------------------------------**----------- Private Sub cmdEdit_Click() Dim iRow As Integer iRow = ComboBox1.ListIndex ComboBox1.RowSource = "" iRow = iRow + 1 With Worksheets("Data") .Cells(iRow, 2) = TextBox1.Value 'This is column B .Cells(iRow, 3) = TextBox2.Value 'This is column C .Cells(iRow, 4) = TextBox3.Value 'This is column D .Cells(iRow, 5) = TextBox4.Value 'This is column E .Cells(iRow, 6) = TextBox5.Value 'This is column F .Cells(iRow, 7) = TextBox6.Value 'This is column G .Cells(iRow, 8) = TextBox7.Value 'This is column H .Cells(iRow, 9) = TextBox8.Value 'This is column iRow .Cells(iRow, 10) = TextBox9.Value 'This is column J .Cells(iRow, 11) = TextBox10.Value 'This is column K .Cells(iRow, 12) = TextBox11.Value 'This is column L .Cells(iRow, 13) = TextBox12.Value 'This is column M End With ComboBox1.RowSource = "B1:M10" End Sub Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(0, i) 'This is column B TextBox2.Value = ComboBox1.Column(1, i) 'This is column C TextBox3.Value = ComboBox1.Column(2, i) 'This is column D TextBox4.Value = ComboBox1.Column(3, i) 'This is column E TextBox5.Value = ComboBox1.Column(4, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(6, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(8, i) 'This is column J TextBox10.Value = ComboBox1.Column(9, i) 'This is column K TextBox11.Value = ComboBox1.Column(10, i) 'This is column L TextBox12.Value = ComboBox1.Column(11, i) 'This is column M End Sub Private Sub UserForm_Activate() ComboBox1.ColumnCount = 12 ComboBox1.ColumnWidths = "30;30;30;30;30;30;30;30;30;30;30;30" ComboBox1.RowSource = "B1:M10" End Sub ---------------------------------------------------------------------------**----------- ---------------------------------------------------------------------------**------------ " wrote: On Jun 26, 9:53 wrote: Syed, Are you saying that you populate a list from columns; in excel worksheet and then edit these columns in worksheets using this list box ? If that is the case then You can user range object of worksheet to point to a particular cell. Array value from this code that updates the list box can be passed to the cell reference and that will update list box as well as cell in worksheet. application.worksheets(name of worksheet).range( refer to cell with 'col A' and 'Row i' here).value = sNewValueCol1 application.worksheets(name of worksheet).range( refer to cell with 'col B' and 'Row i' here).value = sNewValueCol2 Hope this helps Nayan " wrote: On Jun 25, 10:30 wrote: Here is all the code you need for updating multicolumn list box Insert a listbox and 2 cmd controls as listed in code below. Hope this helps, Nayan ---------------------------------------------------------------------------**---- Private Sub CommandButton3_Click() Dim sNewValueCol1 As String Dim sNewValueCol2 As String Dim sNewValueCol3 As String sNewValueCol1 = InputBox("Change the values in Col 1", "Edit", "") sNewValueCol2 = InputBox("Change the values in Col 2", "Edit", "") i = ListBox1.ListIndex ListBox1.List(i, 0) = sNewValueCol1 ListBox1.List(i, 1) = sNewValueCol2 End Sub ---------------------------------------------------------------------------**---------------------- Private Sub CommandButton1_Click() Dim sMyRange as string sMyRange = ActiveSheet.Range("A65536").End(xlUp).Row Dim ListArray() MyArray = Range("A1:B" & sMyRange ) ListBox1.ColumnCount = 2 ListBox1.ColumnWidths = "20;20" Me.ListBox1.List = MyArray End Sub ---------------------------------------------------------------------------**----- "Nayan" wrote: Hi , You may not be able to edit the multicolumn directly. See the code below that will let you edit for a single col and you may extend that for multiple col Good Luck Nayan ---------------------------------------------------------------------------**------------------ On a user form put a listbox and 2 command buttons... Private Sub CommandButton1_Click() ListBox1.AddItem "USA" ListBox1.AddItem "Canada" ListBox1.AddItem "London" End Sub Private Sub CommandButton2_Click() Dim sNewListValue As String sNewListValue = InputBox("Change value", "Edit List", ListBox1.List(ListBox1.ListIndex)) ListBox1.RemoveItem ListBox1.ListIndex ListBox1.AddItem sNewListValue End Sub ---------------------------------------------------------------------------**---------------------- " wrote: I have a listbox in a userform, I want to edit listbox records, I have one solution to edit listbox first column, but I dont know how to edit all Listbox columns. does any one have any idea, please help me. Thanks in Advance. Shahzad- Hide quoted text - - Show quoted text - Hi,Nayan, Thank you for your support and prompt reply. I tried your code but it is update only userform, not in the worksheet. Can you pls check again when I Edit multicolumn as per your code, then worksheet data should be changed. Waiting for your reply. Thanks and Regards. syedshahzad- Hide quoted text - - Show quoted text - Dear Mr.Nayan, I am very happy to see that you sent me reply within one hour. I appreciated ... Sir, the code you sent, I checked but it is not working in my form. May be I am doing some thing wrong. Actually I have a Worksheet name is DATA, having Inventory Data from A2:M99 and I have a userform1, showing one combobox1 and other textboxes to display the data. I am using combobox to retrive data, by using this code: Option Explicit Dim i As Integer Private Sub ComboBox1_Change() i = ComboBox1.ListIndex TextBox1.Value = ComboBox1.Column(1, i) 'This is column B TextBox2.Value = ComboBox1.Column(2, i) 'This is column C TextBox3.Value = ComboBox1.Column(3, i) 'This is column D TextBox4.Value = ComboBox1.Column(4, i) 'This is column E TextBox5.Value = ComboBox1.Column(5, i) 'This is column F TextBox6.Value = ComboBox1.Column(6, i) 'This is column G TextBox7.Value = ComboBox1.Column(7, i) 'This is column H TextBox8.Value = ComboBox1.Column(8, i) 'This is column I TextBox9.Value = ComboBox1.Column(9, i) 'This is column J TextBox10.Value = ComboBox1.Column(10, i) 'This is column K TextBox11.Value = ComboBox1.Column(11, i) 'This is column L TextBox12.Value = ComboBox1.Column(12, i) 'This is column M End Sub Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() ComboBox1.SetFocus ComboBox1.RowSource = "A2:N99" End Sub NOW, the problem is this after retriving my records from the worksheet, I am unable to Edit, if I want to change any record (textbox) how I can Edit my records in userform and the worksheet at the same time. this is my big problem. If you send me the solution, I will be highly appreciated. Very sorry to distrub you again and again. Thanks and regards. SyedShahzadZafar Madinah - KSA- Hide quoted text - - Show quoted text - Dear Nayan, Thak you for sending me this solution, it is working fine, but when I change some thing in userform it is not replacing the data in worksheet., I want to change the data on the worksheet from the userform, I am still unale to change the records from Userform. pls help me. Regards. Shahzad- Hide quoted text - - Show quoted text - Dear Nayan, Sorry Nayan, I checked again your code in my userform, it is working. once I enter the data in textbox and then I click on the Edit Button, it is changing the worksheet value. it is exectly I needed. Thank you very much for your support. I got the big solution. Thanks again.... Syed shahzad zafar Madinah. KSA. |
| All times are GMT +1. The time now is 07:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com