MsgBox to find & edit a row of data

msexcel2010 , msexcel2007 , msvb Columbus, United States
  • 7 years ago

    I have an Excel 2007 Job Log [Database] and I can find the last used row and enter the new job data via the userform, but the second userform I use won't let me pick a (Job) row to add data. It keeps adding the data to the last row I entered.

    vb Code I'm using is: '--------------------- Private Sub cmdAdd2_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Job Log")

    'find first empty row in database    'This works fine for the last row, but won't let you choose a specific row by A:A.
    iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(0, 0).Row
    'check for a part number
    If Trim(Me.txtJob.Value) = "" Then
      MsgBox "Please enter job number to update."
      Exit Sub
    End If
    'copy the data to the database
    ws.Cells(iRow, 23).Value = Me.txtJob.Value
    ws.Cells(iRow, 15).Value = Me.txtMatl.Value
    ws.Cells(iRow, 14).Value = Me.txtMark.Value
    ws.Cells(iRow, 16).Value = Me.txtPros1.Value
    ws.Cells(iRow, 17).Value = Me.txtPros2.Value
    ws.Cells(iRow, 18).Value = Me.txtPros3.Value
    ws.Cells(iRow, 19).Value = Me.txtPros4.Value
    ws.Cells(iRow, 20).Value = Me.txtInsp.Value
    ws.Cells(iRow, 21).Value = Me.TxtFOB.Value
    ws.Cells(iRow, 22).Value = Me.TxtPack.Value
    'clear the data
    Me.txtJob.Value = ""
    Me.txtMatl.Value = ""
    Me.txtMark.Value = ""
    Me.txtPros1.Value = ""
    Me.txtPros2.Value = ""
    Me.txtPros3.Value = ""
    Me.txtPros4.Value = ""
    Me.txtInsp.Value = ""
    Me.TxtFOB.Value = ""
    Me.TxtPack.Value = ""
    End Sub

    Can someone tell me where I'm going wrong?

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Memory is like an orgasm. It's a lot better if you don't have to fake it.” - Seymour Cray