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
      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?

Post a reply

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

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

Contribute

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