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

 

[ajetrumpet]

this:

Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

is returning the number of rows from column A starting from the last record that has data in it on the sheet and looking upward to row #1. this

For y = 2 To x

runs a conditional loop starting at a counter of 2 and repeating until the number assigned to the last row of data captured in the previous code. the only 2 places in your update routine that could possibly causing the problem are the only 2 places that are you assigning cells actual values. those lines are:

Sheets("MyCars").Cells(y, 1) = tbxYear.text

and:

Call MyCarsCombined

the content in the latter is unknown since you didn't post that function's code. I didn't look over this part closely, but it also looks like you are assigning values correctly to the combo *cbxdrpdwn*. does that help you along?

 

[erutherford]

Thank you for the reply. I'll have to read up on conditional loops (relatively new at "writing" code). The "call MyCarsComined" is a macro the joins a couple of cells together for the drop down.
So I am thinking the issue is with

Sheets("MyCars").Cells(y, 1) = tbxYear.text

The dropdown just populates the textboxes, then the above code is used to update it. Can't be that complex, but obviously I'm not seeing it.
the battle continues.........

 

[ajetrumpet]

well it doesn't look that bad. because this:

Sheets("MyCars").Cells(y, 1)

IS pointing to cell. and this:

= tbxYear.Text

could possibly be it, but only if you've got erroneous stuff or nothing in the text box at all. don't ya think?

 

[erutherford]

I agree. I don't get any errors (just doesn't update the record), so there has to be a conflict somewhere. I am simplifying the code down to bare bones to see if it will function as it should. I'll keep you posted.

 

[Fluff]

What are the values in Col A & col D?

 

[erutherford]

I set all columns to General

 

[erutherford]

I made the following changes in the code to see what might be causing it to "not update a record" See the change below. I type in "tbxYear" with a bunch of "x"s and hit update and all records in that column are updated with the bunch of "x"s

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 - this line taken out of the function
Sheets("MyCars").Cells(y, 1) = tbxYear.Text
Sheets("MyCars").Cells(y, 2) = tbxMake.Text
Sheets("MyCars").Cells(y, 3) = tbxModel.Text
Sheets("MyCars").Cells(y, 5) = tbxLicense.Text

'End If
End If
Next y

'Call MyCarsCombined

End Sub

That tells me that this code is the problem

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

I am thinking its not comparing "apples to apples"

 

[Fluff]

I set all columns to General

Yes but what are the actual values?

 

[erutherford]

Column A is "tbxYear" contains a four digit number "1979"

Column D is created by a macro. The results are "1997 Ford WTB99" Combines "year make and license"

Sub MyCarsCombined()
Dim lngLastRow As Long

Application.ScreenUpdating = False

'Uses Column A to set the 'lngLastRow' variable _
(find the last row) - change if required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    Range("D2:D" & lngLastRow).Value = Evaluate("=A2:A" & lngLastRow & "&"" ""&" & "B2:B" & lngLastRow & "&"" ""&" & "E2:E" & lngLastRow)
Application.ScreenUpdating = True
End Sub