VBA to add a new row in reference to a specific row/cell

[mwilburn]

I have a worksheet that has my companies action items and client action items. I have macros that add new copied rows with conditional formatting and formulas. The macros I have work but they add a new row under a specified row (18 and 29) which obviously causes issues if I add 4 or 5 rows to the top section.

In short, what I need to do is, instead of adding a row below Row 29, I need it to add a row in 2 rows below wherever the button is located. Is that possible?

Here is what I have, and yes, it's very simple. I am new to macros and VBA and all of this. Thank you for your time.

Sub AddNewRowAgendaTop()
'
' AddNewRowAgendaTop Macro
'


'
Rows("18:18").Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub


Sub AddNewRowAgendaBottom()
'
' AddNewRowAgendaBottom Macro
'


'
Rows("29:29").Select
Application.CutCopyMode = False
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub

 

[Logit]

.

Sub insertrows()
    ActiveCell.EntireRow.Offset(1).Resize(2).Insert Shift:=xlDown
End Sub

 

[mwilburn]

This is almost there. It does paste a new row to the same place in reference to the cell and not to a designed row (which is awesome) but how do I get it to copy and paste in a row with the formatting?

.

Sub insertrows()
    ActiveCell.EntireRow.Offset(1).Resize(2).Insert Shift:=xlDown
End Sub

 

[Logit]

.
After reading your first post again, I'm not certain I understand the goal. Perhaps you can restate the need in a different way ?

 

[mwilburn]

So, I need to click the Add New Row button and it add a new Row similar to the ones below them (the checkbox, Action Item, Grade and so on). I would like it to add a new row at the top of each section (2 rows below the Add New Row button if possible). If not, I can try and teach the end users to select a cell and then click the button, that's just asking a lot. LOL

.
After reading your first post again, I'm not certain I understand the goal. Perhaps you can restate the need in a different way ?

 

[Logit]

.
Ok .. this is what I've been able to cobble together. Admittedly the checkbox is not centered in the cell in Col A.
I've research numerous resources and everything I've located simply doesn't work. Probably something I am
overlooking.

Option Explicit




Sub AddRow()
    Dim ws As Worksheet
    Dim rng As Range
    Dim I As Integer
    Dim chkBox As CheckBox
   


    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")        '<-- NOTE: Insert Sheet name here.


    On Error Resume Next
    
    With ws
        '~~> Set your range
        Set rng = .Rows(4)      '<-- this number dictates where row will be added
        
        '~~> Insert the range
        rng.Insert Shift:=xlDown
                                                
        rng.Offset(-1, 0).Columns(1).Select
        Set chkBox = ActiveSheet.CheckBoxes.Add(Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=ActiveCell.Width, Height:=ActiveCell.Height)
        chkBox.Caption = ""
               
       
    End With
End Sub

 

[mwilburn]

I should have specified. The check boxes are just characters. They were too hard to work with and I found a shortcut on another forum. They are Wingdings and I have a VBA that switches between characters when I double click (not sure VBA is the correct term).

If those are just characters, does that change your response drastically?

Thank you again so much for your help! It is greatly appreciated.

.
Ok .. this is what I've been able to cobble together. Admittedly the checkbox is not centered in the cell in Col A.
I've research numerous resources and everything I've located simply doesn't work. Probably something I am
overlooking.

Option Explicit




Sub AddRow()
    Dim ws As Worksheet
    Dim rng As Range
    Dim I As Integer
    Dim chkBox As CheckBox
   


    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")        '<-- NOTE: Insert Sheet name here.


    On Error Resume Next
    
    With ws
        '~~> Set your range
        Set rng = .Rows(4)      '<-- this number dictates where row will be added
        
        '~~> Insert the range
        rng.Insert Shift:=xlDown
                                                
        rng.Offset(-1, 0).Columns(1).Select
        Set chkBox = ActiveSheet.CheckBoxes.Add(Left:=ActiveCell.Left, Top:=ActiveCell.Top, Width:=ActiveCell.Width, Height:=ActiveCell.Height)
        chkBox.Caption = ""
               
       
    End With
End Sub

 

[Logit]

.
Can you post your code that inserts the WingDing ?

 

[mwilburn]

Here is what I use but if I copy the row it's in, it works automatically so that is not a concern.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
If Target.Value = Chr(254) Then
Target.Value = Chr(168)
Cancel = True
ElseIf Target.Value = Chr(168) Then
Target.Value = Chr(254)
Cancel = True
End If
End If
End Sub

.
Can you post your code that inserts the WingDing ?

 

[Logit]

.
I looked up a chart of ASCII characters .... can't say I recognize those characters.

What are they ?

Never mind, just found another chart of WingDings that relate to the ASCII characters.
One is a checkbox (checked) and the other is a checkbox (unchecked).

So you don't require your code to be worked into the macro ? Is that what you are saying ?