Buttons to add lines

[Glio]

In the attached worksheet I want to be able to add new rows by clicking on the 'Add Line' buttons. When I click the button the first time it adds the new lrow at line 23, but each time after that it inserts a line at row 23, not at the bottom of the section.

The second Add Line button works in the same way. I have tried to write the VB so that the line number is incrimented for every click of the buttons, as you can see from the VB.

I know it's possible, but I am not yet capable!

AD263b.zip

Any help would appreciated.

Regards

Geoff Culbertson
Petersfield, Hampshire, UK

[Leith Ross]

Hello Glio,

The macros now allow for you delete lines, and still add new lines above the last formatted line. The macros have already been added to your buttons in the attached workbook.

Private Sub CommandButton1_Click()
  
  Dim R As Long
  
    R = 21
    While Cells(R, "C").MergeCells = True
      R = R + 1
    Wend
    
      Cells(R - 1, "C").EntireRow.Copy
      Cells(R, "C").EntireRow.Insert
    Application.CutCopyMode = False
    
End Sub

Private Sub CommandButton2_Click()

  Dim R As Long
  Dim Rng As Range
  
    Set Rng = Range("C21:C10000").Find(What:="name", LookIn:=xlValues, LookAt:=xlWhole, _
                                 SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
      If Rng Is Nothing Then
         MsgBox "Apologies area not found or missing."
         Exit Sub
      End If
    
    R = Rng.Row + 1
      While Cells(R, "C").MergeCells = True
        R = R + 1
      Wend
    
      Cells(R - 1, "C").EntireRow.Copy
      Cells(R, "C").EntireRow.Insert
    Application.CutCopyMode = False
        
End Sub

Sincerely,
Leith Ross

[Glio]

Leith,
Many thanks for your code. As you could see from my file I had been trying all sorts of ways to do what I wanted but without success. Your code works beautifully. I will be using it again in another worksheet and will try to augment it as well.

Thanks again

Geoff Culbertson
Petersfield, Hampshire, UK

[Leith Ross]

Hello Geoff,

Glad to hear it worked out for you. If you have any questions about what the code does, or need anything else, just ask.

Sincerely,
Leith Ross

[Glio]

Leith,
As you can see from the attached file, I have changed your code slightly on the Operation tab to clear the contents of the new line inserted, and I have used the same code on the PostEvent tab. All works well.

However on the Chronology tab I have tried to do the same thing and failed

On this tab the buttons work properly if you use any one button alone. This must be because I have hard-coded the row number for the start of each area.

I have tried to alter your code starting Set Rng and every time get the Message Box! As I don't really understand what this part of your code is doing (apart from working on the first tab!) I cannot make suitable changes on the second tab.

As you can see I have commented out the code which did not work and I hope you can see why I have changed some of it, especially for the General Observations & Observation lines which do not have merged cells in column B.

Hope you can help again!

Best regards

Geoff Culbertson
Petersfield, Hampshire, UK

[Leith Ross]

Hello Geoff,

Sorry to keep you waiting. I completely rewrote the macros. It should be easier and faster to do your coding now. All the code resides in two modules: a Standard Module and a Class Module. Once you add a new button, you only need to add it to the command button setup list module. The entry lines must be to the left of the button, and the line color must be the yellow that you used, which has a color index of 36. Everything has been added to the attached workbook.

CmdBtnClass Class Module
This customizes the ActiveX (Control Toolbox) added to the workbook to simplify adding lines to the worksheets. After adding a new Class Module to your project, rename it CmdBtnClass.

'Written: November 25, 2007
'Author: Leith Ross
'Summary: Custom CommandButton Class

Private WithEvents MSCmdBtn As MSForms.CommandButton
'----------------------------------------'
' Private Class Variables and Objects    '
'----------------------------------------'
  Dim pvtButtonName As String
  Dim pvtWorksheetName As String
  Dim pvtHasHeaderRow As Boolean
  Dim oleCmdBtn As OLEObject

'----------------------------------------'
' Read/Write Properties                  '
'----------------------------------------'
    Public Property Let ButtonName(Button_Name As String)
      pvtButtonName = Button_Name
    End Property
    Public Property Get ButtonName() As String
      ButtonName = pvtButtonName
    End Property
    
    Public Property Let Caption(Caption_Text As String)
      MSCmdBtn = Caption_Text
    End Property
    Public Property Get Caption() As String
      Caption = MSCmdBtn.Caption
    End Property
        
    Public Property Let HasHeaderRow(Has_Header_Row As Boolean)
      pvtHasHeaderRow = Has_Header_Row
    End Property
    Public Property Get HasHeaderRow() As Boolean
      HasHeaderRow = pvtHasHeaderRow
    End Property
    
    Public Property Let Height(Height As Double)
      oleCmdBtn.Height = Height
    End Property
    Public Property Get Height() As Double
      Height = oleCmdBtn.Height
    End Property
    
    Public Property Let Left(Left As Double)
      oleCmdBtn.Left = Left
    End Property
    Public Property Get Left() As Double
      Left = oleCmdBtn.Left
    End Property
    
    Public Property Let Top(Top As Double)
      oleCmdBtn.Top = Top
    End Property
    Property Get Top() As Double
      Top = oleCmdBtn.Top
    End Property
    
    Public Property Let Visible(State As Boolean)
      oleCmdBtn.Visible = State
    End Property
    Public Property Get Visible() As Boolean
      Visible = oleCmdBtn.Visible
    End Property
    
    Public Property Let Width(Width As Double)
      oleCmdBtn.Width = Width
    End Property
    Public Property Get Width() As Double
      Width = oleCmdBtn.Width
    End Property
    
    Public Property Let WorksheetName(Worksheet_Name As String)
      pvtWorksheetName = Worksheet_Name
    End Property
    Public Property Get WorksheetName() As String
      WorksheetName = pvtWorksheetName
    End Property
    
    
'----------------------------------------'
' Read Only Properties                   '
'----------------------------------------'
    Public Property Get BottomRightCell() As Range
      Set BottomRightCell = oleCmdBtn.BottomRightCell
    End Property
    
    Public Property Get OLEIndex() As Long
      OLEIndex = oleCmdBtn.Index
    End Property
    
    Public Property Get OLEType() As Variant
      OLEType = oleCmdBtn.OLEType
    End Property
    
    Public Property Get TopLeftCell() As Range
      Set TopLeftCell = oleCmdBtn.TopLeftCell
    End Property
    
    
'----------------------------------------'
' Methods                                '
'----------------------------------------'
  Public Sub Activate()
    Set oleCmdBtn = Worksheets(pvtWorksheetName).OLEObjects(pvtButtonName)
    Set MSCmdBtn = oleCmdBtn.Object
  End Sub

  Public Function Copy()
    oleCmdBtn.Copy
  End Function

  Public Function Cut()
    oleCmd.Cut
  End Function


'----------------------------------------'
' Event Procedures                       '
'----------------------------------------'
Private Sub Class_Terminate()
  Set MSCmdBtn = Nothing
End Sub

Private Sub MSCmdBtn_Click()

  Dim C As Long
  Dim N As Long
  Dim R As Long
      
      With MSCmdBtn
        C = .TopLeftCell.Column - 1
        R = .TopLeftCell.Row
        If pvtHasHeaderRow = True Then R = R + 1
      End With
      
       'Count the pale yellow lines
        Do While Cells(R + N, C).Interior.ColorIndex = 36
          N = N + 1
        Loop
      
      If N <> 0 Then
         Cells(R + N, "A").EntireRow.Insert
         Cells(R + N - 1, "A").EntireRow.Copy
         Cells(R + N, "A").PasteSpecial
         Application.CutCopyMode = False
      End If
      
End Sub

Setup Command Buttons
After you add a button to a worksheet you will need to add a reference to that button this module. This creates a new custom command button that will automatically add lines when clicked. The row of the first entry line is the row of the upper left corner that the button is in. This first row can be a header row. If it is you need to set the HasHeaderRow property to True. The Default is False. You will see examples of this in the module code.

Dim CmdBtn1 As New CmdBtnClass
Dim CmdBtn2 As New CmdBtnClass
Dim CmdBtn3 As New CmdBtnClass
Dim CmdBtn4 As New CmdBtnClass
Dim CmdBtn5 As New CmdBtnClass

Sub SetupCommandButtons()

  With CmdBtn1
    .ButtonName = "CommandButton1"
    .HasHeaderRow = True
    .WorksheetName = "Operation"
    .Activate
  End With
  
  With CmdBtn2
    .ButtonName = "CommandButton2"
    .HasHeaderRow = True
    .WorksheetName = "Operation"
    .Activate
  End With
    
   With CmdBtn3
    .ButtonName = "Chronology"
    .HasHeaderRow = False
    .WorksheetName = "Chronology"
    .Activate
  End With
  
  With CmdBtn4
    .ButtonName = "GenObservations"
    .HasHeaderRow = False
    .WorksheetName = "Chronology"
    .Activate
  End With
  
  With CmdBtn5
    .ButtonName = "Observation"
    .HasHeaderRow = False
    .WorksheetName = "Chronology"
    .Activate
  End With
    
End Sub

This macro macro is also called by the Workbook_Open() and Workbook_BeforeSave() events. If for some reason the buttons stop working, run this macro.

Sincerely,
Leith Ross