MsgBox to find & edit a row of data

• • Reply

  10 years ago

  by mtcengineering

  Tony Casillas

  Westerville, United States

  Joined 10 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
    Me.txtJob.SetFocus
    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 = ""
  Me.txtJob.SetFocus
  
  End Sub
  

  Can someone tell me where I'm going wrong?

  Report abuse