Insert New Row With New Linked Checkbox

[Man_of_Sleep]

Hi,

I have this code to insert new row button.

Option Explicit
 
Private Sub CommandButton1_Click()
    addNewRow
End Sub
 
Sub addNewRow()
    
    ' Do not insert a row before the first row.
    Dim iTopRow As Integer
    iTopRow = 1
    
    If (ActiveCell.Row > iTopRow) Then

        ' Get the active row number.
        Dim rowNum As Integer
        rowNum = ActiveCell.Row
        
        Rows(rowNum).EntireRow.Insert       ' Insert a new row.
        
    End If
End Sub

Then I am trying to insert the new row, with a new linked checkbox.
The checkbox is automatic linked to the new cell in the new added row.

 

[GWteB]

How about (to be pasted in the module of the worksheet to be affected, like you did)

Sub addNewRow_r2()
    
    ' Do not insert a row before the first row.
    Const TopRow As Long = 1
    
    ' Get the active row number.
    Dim rowNum As Long
    rowNum = ActiveCell.Row
    
    If (rowNum > TopRow) Then

        Rows(rowNum).Insert       ' Insert a new row.
        
        ' === add a Check Box ===
        Dim oCB As CheckBox
        Dim c   As Range
        
        Set c = Cells(rowNum, 1)
        With c
            Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)
            oCB.LinkedCell = .Address
        End With
    End If
End Sub

 

[Man_of_Sleep]

wow.. it work really well and great.
i am truly amazing with members in this forum.

Thank you very much GWteB.

 

[Man_of_Sleep]

sorry..

Can you help me one more?
How to make the checkbox only appear the box when I click the add row button?

I don't want the text 'check box' beside the box.
How to automatically delete them?

Thanks in advance again.

 

[GWteB]

The displayed text "Check Box 1" is the value of the check box's Caption property and can be cleared easily.
In the last section of my post #2 code, insert the red line of code as per below:

        With c
            Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)
            oCB.LinkedCell = .Address
            oCB.Caption = vbNullString
        End With

 

[Man_of_Sleep]

wow.. before I asked the question, I've spent 3 hours to figured the solution due to my zero VBA knowledge.
You solved with just 1 line.

Thank you very much GWteB.

 

[GWteB]

You are welcome and thanks for letting me know.

 

[Man_of_Sleep]

Dear GWteB,

Sorry, for pop-up this thread.
I am searching for the solution but i cant find.

I make new excel file.
Then I copy and paste the above code in the module.
But when I run the code, get Run-time error "424'. Object required.

With this line highlighted:
Set oCB = CheckBoxes.Add(.Left, .Top, .Width, .Height)

Can you help me how to solve this issue.

Thank you very much.

 

[Nathalia508]

Dear GWteb

Please help me with this.

When I copy the VBA above and run it in my excel the check box will not be inserted to all the rows column.
if i inserted raw only jan or april is having check box but the rest of month check box is not been inseted.

Thank you very much for your help.