none
Excel User Form RRS feed

  • Question

  • I have a user form that creates a new record whenever I run it. I would like it to just enter the data into the same cells whenever I run it, instead of creating a new record.

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")
    
    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
      .End(xlUp).Offset(1, 0).Row
    
    'check for a part number
    If Trim(Me.txtPart.Value) = "" Then
      Me.txtPart.SetFocus
      MsgBox "Please enter a part number"
      Exit Sub
    End If
    
    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.txtPart.Value
    ws.Cells(iRow, 2).Value = Me.txtLoc.Value
    ws.Cells(iRow, 3).Value = Me.txtDate.Value
    ws.Cells(iRow, 4).Value = Me.txtQty.Value
    
    'clear the data
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtDate.Value = ""
    Me.txtQty.Value = ""
    Me.txtPart.SetFocus
    
    End Sub
    Wednesday, January 11, 2012 6:01 AM

Answers

  • Well, erase the line looking for empty cells, and just hardcode the cell position to fill the Form data, for example:

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")
    
    ' // ignore this line
    'find first empty row in database
    'iRow = ws.Cells(Rows.Count, 1) _
     ' .End(xlUp).Offset(1, 0).Row
    
    'check for a part number
    If Trim(Me.txtPart.Value) = "" Then
      Me.txtPart.SetFocus
      MsgBox "Please enter a part number"
      Exit Sub
    End If
    
    ' // write to hardcoded cell position
    'copy the data to the database
    ws.Cells(1, 1).Value = Me.txtPart.Value
    ws.Cells(1, 2).Value = Me.txtLoc.Value
    ws.Cells(1, 3).Value = Me.txtDate.Value
    ws.Cells(1, 4).Value = Me.txtQty.Value
    
    'clear the data
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtDate.Value = ""
    Me.txtQty.Value = ""
    Me.txtPart.SetFocus
    
    End Sub



    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Friday, January 13, 2012 7:11 PM
    Moderator

All replies

  • Hello,

    Be aware this is a forum for VB in Visual Studio Net.

    The forum for VBA is Isvvba

    http://social.msdn.microsoft.com/Forums/en-US/isvvba


    Success
    Cor
    Wednesday, January 11, 2012 7:55 AM
  • I would post your question to the Excel for Developers forum. This forum is primarily for Visual Basic .NET.

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, January 12, 2012 1:19 PM
  • Well, erase the line looking for empty cells, and just hardcode the cell position to fill the Form data, for example:

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")
    
    ' // ignore this line
    'find first empty row in database
    'iRow = ws.Cells(Rows.Count, 1) _
     ' .End(xlUp).Offset(1, 0).Row
    
    'check for a part number
    If Trim(Me.txtPart.Value) = "" Then
      Me.txtPart.SetFocus
      MsgBox "Please enter a part number"
      Exit Sub
    End If
    
    ' // write to hardcoded cell position
    'copy the data to the database
    ws.Cells(1, 1).Value = Me.txtPart.Value
    ws.Cells(1, 2).Value = Me.txtLoc.Value
    ws.Cells(1, 3).Value = Me.txtDate.Value
    ws.Cells(1, 4).Value = Me.txtQty.Value
    
    'clear the data
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtDate.Value = ""
    Me.txtQty.Value = ""
    Me.txtPart.SetFocus
    
    End Sub



    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Friday, January 13, 2012 7:11 PM
    Moderator