I have a sheet called "Player Info". And the data on this sheets gets entered in from a userform. The code below works great but what I would like to do is when the user clicks a button on the userform (commandButton3), I would like to update the info on the Player Info sheet with the new data entered in from the userform. Right now it just adds another row with the same data. For instance if I want to change the data in textbox3 from AM to AL right now it just adds all the data again. Sorry I forgot to mention that This userform is brought up by double clicking a cell and auto fills in with the data already on the sheet. This code is below the first code.
Private Sub CommandButton3_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Player Info")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(iRow, 2).Value = Me.TextBox1.Value
If CheckBox1 = True Then
.Cells(iRow, 3).Value = "(s)" & " " & Me.TextBox2.Value
Else
If CheckBox2 = True Then
.Cells(iRow, 3).Value = "(c)" & " " & Me.TextBox2.Value
Else
If CheckBox3 = True Then
.Cells(iRow, 3).Value = "(s/s)" & " " & Me.TextBox2.Value
Else
.Cells(iRow, 3).Value = Me.TextBox2.Value
End If
End If
End If
.Cells(iRow, 4).Value = Me.TextBox3.Value
.Cells(iRow, 5).Value = Me.TextBox4.Value
.Cells(iRow, 6).Value = Me.TextBox5.Value
.Cells(iRow, 7).Value = Me.TextBox6.Value
.Cells(iRow, 8).Value = Me.TextBox7.Value
.Cells(iRow, 9).Value = Me.TextBox8.Value
.Cells(iRow, 15).Value = Me.TextBox9.Value
.Cells(iRow, 16).Value = Me.TextBox10.Value
.Cells(iRow, 17).Value = Me.TextBox17.Value
.Cells(iRow, 18).Value = Me.TextBox18.Value
.Cells(iRow, 19).Value = Me.TextBox19.Value
.Cells(iRow, 20).Value = Me.TextBox25.Value
.Cells(iRow, 21).Value = Me.TextBox24.Value
.Cells(iRow, 22).Value = Me.TextBox26.Value
.Cells(iRow, 23).Value = Me.TextBox39.Value
.Cells(iRow, 24).Value = Me.TextBox28.Value
.Cells(iRow, 25).Value = Me.TextBox29.Value
.Cells(iRow, 26).Value = Me.TextBox31.Value
.Cells(iRow, 27).Value = Me.TextBox35.Value
.Cells(iRow, 28).Value = Me.TextBox32.Value
If TextBox11 & TextBox12 > "" Then
.Cells(iRow, 12).Value = Me.TextBox11.Value & " " & Me.TextBox12.Value
End If
If TextBox13 & TextBox14 > "" Then
.Cells(iRow, 13).Value = Me.TextBox13.Value & " " & Me.TextBox14.Value
End If
If TextBox15 & TextBox16 > "" Then
.Cells(iRow, 14).Value = Me.TextBox15.Value & " " & Me.TextBox16.Value
End If
End With
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox25.Value = ""
Me.TextBox24.Value = ""
Me.TextBox26.Value = ""
Me.TextBox39.Value = ""
Me.TextBox28.Value = ""
Me.TextBox29.Value = ""
Me.TextBox31.Value = ""
Me.TextBox35.Value = ""
Me.TextBox32.Value = ""
Me.CheckBox4.Value = False
Me.CheckBox5.Value = False
Me.CheckBox6.Value = False
Me.CheckBox7.Value = False
Me.CheckBox8.Value = False
Me.CheckBox9.Value = False
Me.CheckBox10.Value = False
Me.CheckBox11.Value = False
Me.CheckBox12.Value = False
Me.CheckBox13.Value = False
Me.CheckBox14.Value = False
Me.CheckBox15.Value = False
Unload Me
ActiveWorkbook.Worksheets("Player Info").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Player Info").Sort.SortFields.Add Key:=Range("D4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Player Info").Sort
.SetRange Range("B4:AB329")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.Intersect(Target, Range("C4:C400")) Is Nothing Then
Dim rng As Range
Dim cl As Range
Dim vFind
Dim R As Long
Cancel = True
UserForm1.TextBox1.Value = ActiveCell.Offset(0, -1).Value
UserForm1.TextBox2.Value = ActiveCell.Offset(0, 0).Value
UserForm1.TextBox3.Value = ActiveCell.Offset(0, 1).Value
UserForm1.TextBox4.Value = ActiveCell.Offset(0, 2).Value
UserForm1.TextBox5.Value = ActiveCell.Offset(0, 3).Value
UserForm1.TextBox6.Value = ActiveCell.Offset(0, 4).Value
UserForm1.TextBox7.Value = ActiveCell.Offset(0, 5).Value
UserForm1.TextBox8.Value = ActiveCell.Offset(0, 6).Value
UserForm1.TextBox9.Value = ActiveCell.Offset(0, 12).Value
UserForm1.TextBox10.Value = ActiveCell.Offset(0, 13).Value
If ActiveCell.Offset(0, 9).Value <> "" Then
UserForm1.CheckBox1.Value = True
UserForm1.TextBox11.Value = ActiveCell.Offset(0, 9).Value
End If
If ActiveCell.Offset(0, 10).Value <> "" Then
UserForm1.CheckBox2.Value = True
UserForm1.TextBox13.Value = ActiveCell.Offset(0, 10).Value
End If
If ActiveCell.Offset(0, 11).Value <> "" Then
UserForm1.CheckBox3.Value = True
UserForm1.TextBox15.Value = ActiveCell.Offset(0, 11).Value
End If
UserForm1.Show
End If
End Sub
Thanks for the help in advance
Excel 2010
Bookmarks