i have written this code and i want to choose the values from the
range "C" from a list box to edit or delete them, but i have many
problems, first of all the code should be restarted in order to take
the new aded values each time to enter the list box, but i need it to
regenerate automaticaly, also i want when i click a value from the
list i want its values to go like that
value in excelsheet of A goes to textbox1
value in excelsheet of B goes to textbox2
value in excelsheet of C goes to textbox3
value in excelsheet of D goes to textbox4

and therefore i want to edit them in these boxs
i also want the delet button to delet the chosen row if pressed
and i want no gaps in the excel sheet each row that is deleted shall
not just get rid of it values and stay empty, no! would anyone help
with that please? and check my code for any mistakes?
please !


Private Function runTests()
If Not IsNumeric(TextBox4) Then
TextBox4.SetFocus
TextBox4.SelStart = 0
TextBox4.SelLength = Len(TextBox4)
MsgBox "You must enter a number in Phone"
runTests = 0
Exit Function
End If
runTests = 1
End Function
Private Sub CommandButton1_Click()
If runTests Then
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Value

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Value = ""

TextBox1.SetFocus

Else
Unload Me
End If
End If
End Sub

Private Sub CommandButton3_Click()
End
End Sub

Private Sub listBox_Click()
Unload Me
End Sub

Private Sub UserForm_activate()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("c2", .Cells(.Rows.Count, "C").End(xlUp))
End With

With Me.ctrlList
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
End Sub

Private Sub Ctrllist_Change()
Dim i As Integer
If Not Cleared Then
With Sheets(1)
i = Application.Match(Me.ctrlList.Value, Range("C2"), 0)
Cleared = True
.Range("C2")(i).EntireRow.Delete Shift:=xlUp

End With
End If
Cleared = False
Me.Hide
End Sub

You should clear listbox before adding items with
Me.ctr.clear

I had a similar problem. I kept an auxilary array which kept copy of the
list box with additional data I needed. In you case if you had an array
which had the sheetname of the items in the listbox.


"TheGodfather" wrote:

i have written this code and i want to choose the values from the
range "C" from a list box to edit or delete them, but i have many
problems, first of all the code should be restarted in order to take
the new aded values each time to enter the list box, but i need it to
regenerate automaticaly, also i want when i click a value from the
list i want its values to go like that
value in excelsheet of A goes to textbox1
value in excelsheet of B goes to textbox2
value in excelsheet of C goes to textbox3
value in excelsheet of D goes to textbox4

and therefore i want to edit them in these boxs
i also want the delet button to delet the chosen row if pressed
and i want no gaps in the excel sheet each row that is deleted shall
not just get rid of it values and stay empty, no! would anyone help
with that please? and check my code for any mistakes?
please !


Private Function runTests()
If Not IsNumeric(TextBox4) Then
TextBox4.SetFocus
TextBox4.SelStart = 0
TextBox4.SelLength = Len(TextBox4)
MsgBox "You must enter a number in Phone"
runTests = 0
Exit Function
End If
runTests = 1
End Function
Private Sub CommandButton1_Click()
If runTests Then
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Value

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Value = ""

TextBox1.SetFocus

Else
Unload Me
End If
End If
End Sub

Private Sub CommandButton3_Click()
End
End Sub

Private Sub listBox_Click()
Unload Me
End Sub

Private Sub UserForm_activate()
Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("c2", .Cells(.Rows.Count, "C").End(xlUp))
End With

With Me.ctrlList
For Each myCell In myRng.Cells
.AddItem myCell.Text
Next myCell
End With
End Sub

Private Sub Ctrllist_Change()
Dim i As Integer
If Not Cleared Then
With Sheets(1)
i = Application.Match(Me.ctrlList.Value, Range("C2"), 0)
Cleared = True
.Range("C2")(i).EntireRow.Delete Shift:=xlUp

End With
End If
Cleared = False
Me.Hide
End Sub