CurrentRow for a next, previous or update button

HUNTING101

New Member
Joined
Jan 7, 2007
Messages
3
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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...
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top