Edit Listbox in Userform

[shahzad4u_ksa]

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

 

[JLGWhiz]

You can probably use the List property to edit. Check it out in VBA help.

 

[shahzad4u_ksa]

You can probably use the List property to edit. Check it out in VBA help.








- 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

 

[Nayan]

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

 

[Nayan]

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

------------------------------------------------------------------------------

 

[shahzad4u_ksa]

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

---------------------------------------------------------------------------­---















- 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

 

[Nayan]

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

 

[shahzad4u_ksa]

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











- 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

 

[Nayan]

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

 

[shahzad4u_ksa]

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

---------------------------------------------------------------------------­-----------
---------------------------------------------------------------------------­------------


























- 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

 

[shahzad4u_ksa]

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.