2 questions regarding

[erutherford]

build a simple sub that works fine updating records

Private Sub CommandButton2_Click() 'update
Dim x As Long
Dim y As Long
x = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("Sheet1").Cells(y, 1).Text = TextBox2.Value Then
Sheets("Sheet1").Cells(y, 1) = TextBox1.Text
End If
Next y
End Sub

1 of 2 questions: What is "For Y = 2 to x" saying? Y referring to Sheet1, column.....

Next I tried to plug that structure into my form, won't update the record

Private Sub CommandButton1_Click() 'Update record
Dim x As Long
Dim y As Long
Dim answer As String
    answer = MsgBox("Are you sure you want to update record?", vbQuestion + vbYesNo + vbDefaultButton2, "Update Record")
x = Sheets("MyCars").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If answer = vbYes Then

If Sheets("MyCars").Cells(y, 1).Text = cbxdrpdwn.Value Then
Sheets("MyCars").Cells(y, 1) = tbxYear.Text

End If
End If
Next y

Call MyCarsCombined

End Sub

Just in case here is the code that resides in the form_initialize

Private Sub UserForm_Initialize()
Dim Lastrow As Long, ws As Worksheet
cbxdrpdwn.Clear
Set ws = Sheets("MyCars")
Lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
Me.cbxdrpdwn.List = ws.Range("D2:D" & Lastrow).Value
currentrow = 2
tbxYear = Cells(currentrow, 1)
tbxMake = Cells(currentrow, 2)
tbxModel = Cells(currentrow, 3)
tbxLicense = Cells(currentrow, 5)
tbxColor = Cells(currentrow, 6)
tbxVIN = Cells(currentrow, 7)
tbxPDate = Cells(currentrow, 8)
tbxPAmt = Cells(currentrow, 9)
tbxPMiles = Cells(currentrow, 10)
tbxInsurDate = Cells(currentrow, 11)
tbxRegDate = Cells(currentrow, 12)

End Sub

Private Sub cbxdrpdwn_Change()
Dim Lastrow As Long, ws As Worksheet
Dim ans As String
ans = Me.cbxdrpdwn.Value
Set ws = Sheets("MyCars")
Lastrow = ws.Range("D" & Rows.Count).End(xlUp).Row
    For i = 2 To Lastrow
        If ws.Cells(i, "D").Value = ans Then
            MsgBox Me.cbxdrpdwn.Value
            Me.tbxYear = ws.Cells(i, "A").Value
            Me.tbxMake = ws.Cells(i, "B").Value
            Me.tbxModel = ws.Cells(i, "C").Value
            Me.tbxLicense = ws.Cells(i, "E").Value
            Me.tbxColor = ws.Cells(i, "F").Value
            Me.tbxVIN = ws.Cells(i, "G").Value
            Me.tbxPDate = ws.Cells(i, "H").Value
            Me.tbxPAmt = ws.Cells(i, "I").Value
            Me.tbxPMiles = ws.Cells(i, "J").Value
            Me.tbxInsurDate = ws.Cells(i, "K").Value
            Me.tbxRegDate = ws.Cells(i, "L").Value
           
            Exit Sub
            End If
    Next i
End Sub

 

[Fluff]

You are loading cbxdrpdwn with the values in col D, but then comparing that to col A.
So if col A is 1997 it will never equal the cbxdrpdwn value of "1997 Ford WTB99"

 

[erutherford]

I though that might be a problem. So it has to be like data? I'll make the change and see what happens. Never thought an update procedure would be so difficult.

Thanks as always Fluff! You are the G.O.A.T!

 

[Fluff]

You're welcome & thanks for the feedback

 

[erutherford]

Quick question. Can you make 3 columns = cbxdrpdwn? For example Col a, b, e = Col D.

 

[Fluff]

Yes you can, but why not just compare the cbxdrpdwn with col D?

 

[ajetrumpet]

I enjoy reading your stuff, Fluff. You are a genius.... although I don't know what a G.O.A.T. is .....

 

[erutherford]

Greatest Of All Time!

 

[erutherford]

I changed the comparison to "D" and that makes things function as they should! See why MVP just isn't enough! LOL

Till next time Fluff, thanks again

 

[Fluff]

You're welcome & thanks for the feedback