I want to pass values from a userform to an excel table. I use an UPDATE button but it only works when i doubleclick on items of the listbox. When i use one click it doesn't work! Any ideas why?Thanks a lot in advance...
---- code for click event in listbox-------
Private Sub ListBox1_Click()
Me.TextBox11.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.ComboBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 24)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
Me.ComboBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 19)
Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
Me.ComboBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 21)
Me.ComboBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 25)
Me.ComboBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 26)
Me.TextBox12.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
End Sub
------- code for update-------
* textbox11 is the id number of the row
Private Sub Update_Click()
If Me.TextBox11.Value = "" Then
MsgBox "Error."
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ÄÕÍÁÌÏËÏÃÉÏ")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(CLng(Me.TextBox11.Value), sh.Range("A:A"), 0)
sh.Range("B" & selected_row).Value = Me.ComboBox1.Value
sh.Range("C" & selected_row).Value = Me.TextBox1.Value
sh.Range("D" & selected_row).Value = Me.TextBox2.Value
sh.Range("O" & selected_row).Value = Me.TextBox3.Value
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Call Refresh_Data
End Sub
---- code for click event in listbox-------
Private Sub ListBox1_Click()
Me.TextBox11.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.ComboBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 13)
Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 24)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 18)
Me.ComboBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 19)
Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
Me.ComboBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 21)
Me.ComboBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 25)
Me.ComboBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 26)
Me.TextBox12.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
End Sub
------- code for update-------
* textbox11 is the id number of the row
Private Sub Update_Click()
If Me.TextBox11.Value = "" Then
MsgBox "Error."
Exit Sub
End If
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ÄÕÍÁÌÏËÏÃÉÏ")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(CLng(Me.TextBox11.Value), sh.Range("A:A"), 0)
sh.Range("B" & selected_row).Value = Me.ComboBox1.Value
sh.Range("C" & selected_row).Value = Me.TextBox1.Value
sh.Range("D" & selected_row).Value = Me.TextBox2.Value
sh.Range("O" & selected_row).Value = Me.TextBox3.Value
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Call Refresh_Data
End Sub