Avatar of Ladydee
Ladydee
 asked on

Add checkboxes based on table column

I had this question after viewing Checkbox - copy row to sheet.

My question is how to add checkboxes based on all value in table column with only one click on the button. below code only add one checkbox. thank you for help.

Sub NewCheckbox()
Dim lRow As Long
Dim target As Range
Dim obj As Object
'Adds a Forms toolbar checkbox to the cell
'Checkbox value is linked to that same cell, using a white font color
    
With Worksheets("Input")
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row       'find  first empty row in database
    Set target = .Cells(lRow, 1)
    With .CheckBoxes.Add(target.Left, target.Top + 1, target.Width, target.Height - 1)
            .Caption = "Fakturer"
            .LinkedCell = target.Address
            .Name = "cb_" & target.Address(False, False)     'Name it like "cb_A11"
            .OnAction = "CheckboxClicked"
    End With
    target.Font.ColorIndex = 2     'White font color for linked cell TRUE/FALSE value
    target.Value = False
End With
End Sub

Open in new window

Microsoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
Roy Cox

8/22/2022 - Mon
Martin Liss

Can you please post a sample that shows where you want to add the checkboxes?
Roy Cox

I would use Marlett or Wingding Fonts to achieve the effect of CheckBoxes, see this example
NotCheckBoxes.xlsm
Ladydee

ASKER
below is the example where I want to add the checkboxes. with only one button click, i want checkboxes added to all row based on table row.

Capture.PNG
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

Sub NewCheckbox()
Dim lRow As Long
Dim target As Range
Dim obj As Object
'Adds a Forms toolbar checkbox to the cell
'Checkbox value is linked to that same cell, using a white font color
    
With Worksheets("Input")
    For lRow = .ListObjects(1).DataBodyRange.Row To .ListObjects(1).DataBodyRange.Rows.Count + _
                                                    .ListObjects(1).DataBodyRange.Row - 1
        Set target = .Cells(lRow, 1)
        With .CheckBoxes.Add(target.Left, target.Top + 1, target.Width, target.Height - 1)
                .Caption = "Fakturer"
                .LinkedCell = target.Address
                .Name = "cb_" & target.Address(False, False)     'Name it like "cb_A11"
                .OnAction = "CheckboxClicked"
        End With
        target.Font.ColorIndex = 2     'White font color for linked cell TRUE/FALSE value
        target.Value = False
    Next
End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ladydee

ASKER
how about i want to use it for all table in multiple worksheet?
Martin Liss

I believe I've answered the question you asked, so please close this question and ask a new question.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
Roy Cox

Pleased to help