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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you please post a sample that shows where you want to add the checkboxes?
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

I would use Marlett or Wingding Fonts to achieve the effect of CheckBoxes, see this example
NotCheckBoxes.xlsm
Avatar of Ladydee
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.

User generated image
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ladydee
Ladydee

ASKER

how about i want to use it for all table in multiple worksheet?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I believe I've answered the question you asked, so please close this question and ask a new question.
SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Pleased to help
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo