updating issue - Help Please

[proctk]

Hi

The following code updates my data base and is suposed to move to next blank row on update, for some reason its not working. It lets me key into enteries and then over writes the second one.

Help please - I had it working at the start.

Here is all the code attached to the userform ( maybe somthing this conflicting.

The userform is suppose to let you enter data retreve the data by clicking a name in a listbox and make changes to it and save the update

So far it lets you enter two items into the data base, retreve enteries, but the update function is not working



Private Sub CommandButton1_Click()
Userform5.Show

End Sub

Private Sub CommandButton10_Click()

Sheets("AddressBook").Activate
Dim a As String, c As Range
a = ListBox1.Value
For Each c In Range("A2:A2000") '(Your row source range)
If c.Value = a Then
If Len(TextBox1) > 0 Then c(1, 4).Value = TextBox1
If Len(TextBox2) > 0 Then c(1, 5).Value = TextBox2
If Len(TextBox3) > 0 Then c(1, 6).Value = TextBox3
If Len(TextBox4) > 0 Then c(1, 9).Value = TextBox4
If Len(TextBox5) > 0 Then c(1, 8).Value = TextBox5
If Len(TextBox6) > 0 Then c(1, 10).Value = TextBox6
If Len(TextBox7) > 0 Then c(1, 15).Value = TextBox7
If Len(TextBox8) > 0 Then c(1, 16).Value = TextBox8
If Len(TextBox9) > 0 Then c(1, 7).Value = TextBox9
If Len(TextBox10) > 0 Then c(1, 11).Value = TextBox10
If Len(ComboBox1) > 0 Then c(1, 17).Value = ComboBox1
End If
Next c

'clear data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.ComboBox1.Value = ""

End Sub

Private Sub CommandButton11_Click()
Sheets("Print").Activate
Range("e8") = TextBox10.Value
Range("e7") = TextBox1.Value
UserForm4.Hide
End Sub

Private Sub CommandButton2_Click()
UserForm7.Show
End Sub
Private Sub CommandButton3_Click()
UserForm8.Show
End Sub
Private Sub CommandButton4_Click()
UserForm9.Show
End Sub
Private Sub CommandButton5_Click()
UserForm10.Show
End Sub

Private Sub CommandButton6_Click()
UserForm11.Show
End Sub

Private Sub CommandButton7_Click()

'Slits name into three coulmns
'Sheets("addressbook").Activate
'Dim MyString As String
'Dim xPos1 As Long, xPos2 As Long

'MyString = TextBox1.Value
'xPos1 = WorksheetFunction.Find(" ", MyString)
'xPos2 = WorksheetFunction.Find(" ", MyString, xPos1 + 1)

'Range("B2") = Left(MyString, xPos1 - 1) '
'Range("C2") = Mid(MyString, xPos1 + 1, xPos2 - xPos1 - 1)
'Range("D2") = Right(MyString, Len(MyString) - xPos2)


'loads info to data Base
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("AddressBook")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

'check for Customer Name
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Name Please"

Exit Sub
End If
ws.Cells(iRow, 4).Value = Me.TextBox1.Value
ws.Cells(iRow, 5).Value = Me.TextBox2.Value
ws.Cells(iRow, 6).Value = Me.TextBox3.Value
ws.Cells(iRow, 7).Value = Me.TextBox9.Value
ws.Cells(iRow, 8).Value = Me.TextBox5.Value
ws.Cells(iRow, 9).Value = Me.TextBox4.Value
ws.Cells(iRow, 10).Value = Me.TextBox6.Value
ws.Cells(iRow, 11).Value = Me.TextBox10.Value
ws.Cells(iRow, 15).Value = Me.TextBox7.Value
ws.Cells(iRow, 16).Value = Me.TextBox8.Value
ws.Cells(iRow, 17).Value = Me.ComboBox1.Value

'clear
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox9.Value = ""
Me.TextBox5.Value = ""
Me.TextBox4.Value = ""
Me.TextBox10.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox6.Value = ""


End Sub



Private Sub CommandButton8_Click()

'Clear Form
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox9.Value = ""
Me.TextBox5.Value = ""
Me.TextBox4.Value = ""
Me.TextBox10.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox6.Value = ""
End Sub

Private Sub CommandButton9_Click()
Unload Me
End Sub

Private Sub ListBox1_change()
Sheets("addressBook").Activate
Dim r As Long
Dim Src As String
r = ListBox1.ListIndex + 1
Src = ListBox1.RowSource

TextBox1.Value = Range(Src).Cells(r, 1)
TextBox2.Value = Range(Src).Cells(r, 5)
TextBox3.Value = Range(Src).Cells(r, 6)
TextBox9.Value = Range(Src).Cells(r, 7)
TextBox5.Value = Range(Src).Cells(r, 8)
TextBox4.Value = Range(Src).Cells(r, 9)
TextBox6.Value = Range(Src).Cells(r, 10)
TextBox10.Value = Range(Src).Cells(r, 11)
TextBox7.Value = Range(Src).Cells(r, 15)
TextBox8.Value = Range(Src).Cells(r, 16)
ComboBox1.Value = Range(Src).Cells(r, 17)
End Sub

Private Sub TextBox4_afterupdate()
TextBox4.Value = Format(TextBox4, "(###) ### - ####")
End Sub
Private Sub TextBox5_afterupdate()
TextBox5.Value = Format(TextBox5, "(###) ### - ####")
End Sub
Private Sub TextBox6_afterupdate()
TextBox6.Value = Format(TextBox6, "(###) ### - ####")
End Sub

Private Sub TextBox9_afterupdate()
TextBox9.Value = Format(TextBox9, "(###) ### - ####")
End Sub

Private Sub UserForm_Initialize()

ComboBox1.List = Array("Business", "Scotia Bank Contact", "Personal")
End Sub

 

[Tazguy37]

I think this might be where your problem lies:

'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

Why not use something like this instead?:

iRow = ws.Range("B65536").End(xlUp).Offset(1, 0).row

Hope that helps!