CurrentRow for a next, previous or update button

[HUNTING101]

I am new to VBA and trying to teach myself so please be kind.

I have a userform and i am trying to set the data in the from as CurrentRow for a next, previous or update button.

Here is a sample of what i have so far under the "Update" button but it is adding a new row to the top of my table and not updating the current data.
any help is appreciated.

Private Sub CommandButton4_Click()

Dim CurrentRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Daily Data")

If Me.TB_Date.Value = "" Then
MsgBox "Date Can Not be Blank!!!", vbExclamation, "Date"
Exit Sub
End If


Sheets("Daily Data").Select
CurrentRow = CurrentRow + 2
Rows(CurrentRow).Select
answer = MsgBox("Do you want to update this record?", vbYesNo + vbQuestion, "Update Record?")
If answer = vbYes Then

ws.Cells(CurrentRow, 1).Value = Me.TB_Date.Value
ws.Cells(CurrentRow, 2).Value = Me.CB_Contractor1.Value
ws.Cells(CurrentRow, 3).Value = Me.CB_Contractor2.Value
ws.Cells(CurrentRow, 4).Value = Me.CB_Contractor3.Value
ws.Cells(CurrentRow, 5).Value = Me.CB_Contractor4.Value
ws.Cells(CurrentRow, 6).Value = Me.CB_Contractor5.Value

 

[NdNoviceHlp]

You need to derive what the current row is... U haven't said how U want to achieve this? U don't need any of that selection stuff, U just need to state what the current row is...

If Me.TB_Date.Value = vbNullString Then
MsgBox "Date Can Not be Blank!!!", vbExclamation, "Date"
Exit Sub
End If
If MsgBox("Do you want to update this record?", vbYesNo + vbQuestion, "Update Record?") = vbYes Then
Set ws = Worksheets("Daily Data")
CurrentRow = 2 ' some row number
ws.Cells(CurrentRow, 1).Value = Me.TB_Date.Value
ws.Cells(CurrentRow, 2).Value = Me.CB_Contractor1.Value
ws.Cells(CurrentRow, 3).Value = Me.CB_Contractor2.Value
ws.Cells(CurrentRow, 4).Value = Me.CB_Contractor3.Value
ws.Cells(CurrentRow, 5).Value = Me.CB_Contractor4.Value
ws.Cells(CurrentRow, 6).Value = Me.CB_Contractor5.Value
End If

HTH. Dave

 

[HUNTING101]

Dave,

Thanks for the help. this really cleans up my code. How to i get the CurrentRow to change with the data i have in my userform? My userform is in putting data into a table, so if call up the next row and change one of the contractors I want it to update the data in that row. Hope that makes sense.

Matt

 

[NdNoviceHlp]

You have to tell the code what CurrentRow is... there is no data in userforms. Does your form list the contactors somewhere? Is it really a table row or just a sheet row? It seems like U have data stored somewhere and U want to be able to retrieve/change a "row" of information. Each row of data represents a contactor. U want to select the contactor somehow and then change their "row" of info. A bit more info is needed. Dave