locked
Adding a row to a defined list RRS feed

  • Question

  • I have a xls sheet in which I add items by row. The following vb code adds this row from a button - click - procedure

    Q: If I create a list around the data that it's inserting - it works fine as long as I "toggle off" the total row. If the "total row" is on - it insets outside the list area.

    Any suggestion on how I  can have "total row' on and insert within the list area?

    Thanks

    Private Sub cmdAddNewCar_Click()
    
    Dim lRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("PartsData")
    
    'find first empty row in database
    lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    lPart = Me.cboPart.ListIndex
    
    'check for a part number
    If Trim(Me.cboPart.Value) = "" Then
      Me.cboPart.SetFocus
      MsgBox "Please enter a part number"
      Exit Sub
    End If
    
    'copy the data to the database
    'use protect and unprotect lines,
    '     with your password
    '     if worksheet is protected
    With ws
    '  .Unprotect Password:="password"
      .Cells(lRow, 1).Value = Me.cboPart.Value
      .Cells(lRow, 2).Value = Me.cboPart.List(lPart, 1)
      .Cells(lRow, 3).Value = Me.cboLocation.Value
      .Cells(lRow, 4).Value = Me.txtDate.Value
      .Cells(lRow, 5).Value = Me.txtQty.Value
    '  .Protect Password:="password"
    End With
    
    'clear the data
    Me.cboPart.Value = ""
    Me.cboLocation.Value = ""
    Me.txtDate.Value = Format(Date, "Medium Date")
    Me.txtQty.Value = 1
    Me.cboPart.SetFocus
    
    End Sub
    

    Tuesday, November 11, 2014 2:11 AM

Answers

  • Any suggestion on how I  can have "total row' on and insert within the list area?

    You can't, you have to turn the total row off before you modify the data.

    ActiveSheet.ListObjects(1).ShowTotals = False

    If you are finished, turn it on, resp. restore the state before the macro has run.

    Andreas.

    Tuesday, November 11, 2014 2:30 PM

All replies

  • Any suggestion on how I  can have "total row' on and insert within the list area?

    You can't, you have to turn the total row off before you modify the data.

    ActiveSheet.ListObjects(1).ShowTotals = False

    If you are finished, turn it on, resp. restore the state before the macro has run.

    Andreas.

    Tuesday, November 11, 2014 2:30 PM
  • Hi,

    I am not sure if this is the answer that you are looking for. For me it is not clear when there should be in row inserted.

    I have an examble that there will be a row inserted on line above the last line.

    Sub InsertRow()

    Dim iRow As Long
    Dim iCol As Integer

    iRow = 1
    iCol = 0

    Sheets("Blad3").Select
    Range("a1").Select

    Do While ActiveCell.Offset <> ""

    If ActiveCell.Offset(iRow, iCol) = "" Then
        iRow = iRow - 1
        Range(ActiveCell.Offset(iRow, 0), ActiveCell.Offset(iRow, 4)).Select
        Selection.Insert shift:=xlDown
        Exit Do
    End If

    iRow = iRow + 1

    Loop

    End Sub

    Regards Reshma

    Tuesday, November 11, 2014 9:37 PM