• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert multiple checkboxes in all sheets at the same time

ladydee

New Member
Hi, I have one questions. I wanna ask if is possible to insert multiple checkboxes in all sheets at the same time? I already done many search on internet about this but I gain nothing. It is possible to solve this using excel vba? or is there any other way? just wonder. Thanks you for any helps.
 
Simple answer. Yes it is possible. If you are looking for assistance here on this forum, you will need provide the specifics of what you are trying to do. How many, which sheets, what cells, etc. Without the specifics we can not supply you with a workable solution.
 
Hi AlanSidman,

Thank you for your reply. first I just wanna sure if there any possible for my question. as you say it possible, how about if:

1. there are about 50 sheets in one workbook.
2. each sheet contain data in table.
3. I want put checkboxes at column F in all sheets and based on last row of the table (which mean last row of data)
4. I want when i click macro, automatically insert checkboxes in all sheets at same time and based on criteria no 3.

hope you can help me. thanks for your help.
 
Looks at Column A for data to determine last row.
Code:
Option Explicit

Sub Ladydee()
    Dim ws As Worksheet, i As Long
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To lr
            ActiveSheet.CheckBoxes.Add(Cells(i, "F").Left, _
                                       Cells(i, "F").Top, _
                                       72, 17.25).Select
            With Selection
                .Caption = ""
                .Value = xlOff    '
                .Display3DShading = False
            End With
        Next i
    Next ws

End Sub
 
Thanks. It works but checkboxes only added in active sheet. other sheets nothing.

how about if data not in table? how to change the code based on last row of data?
 
It is currently set to look at the last row of data in Column A. If you don't have data in Column A, then you will need to select another column or hard code how many rows you want to have this do this for. I will look into the issue of only adding to active sheet and post back.
 
Amended

Code:
Option Explicit


Sub Ladydee()
    Dim ws As Worksheet, i As Long
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To lr
            ws.CheckBoxes.Add(Cells(i, "F").Left, _
                                      Cells(i, "F").Top, _
                                      72, 17.25).Select
            With Selection
                .Caption = ""
                .Value = xlOff    '
              .Display3DShading = False
            End With
        Next i
    Next ws

End Sub
 
It works but only checkboxes in active sheet placed in column F. checkboxes in other sheets placed randomly with caption "checkbox 1" (based on no of checkbox)

picture below show result of checkboxes placed in sheet other than active sheet.

Capture.PNG
 
Fixed> See Below

Code:
Option Explicit


Sub Ladydee()
    Dim ws As Worksheet, i As Long
    Dim lr As Long
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To lr
            ws.CheckBoxes.Add(Cells(i, "F").Left, _
                                      Cells(i, "F").Top, _
                                      72, 17.25).Select
            ws.Activate
            With Selection
                .Caption = ""
                .Value = xlOff    '
            .Display3DShading = False
            End With
        Next i
    Next ws

End Sub
 
Hi AlanSidman,

Its works exactly like what I want. just there problem where checkbox at first row placed randomly while checkbox at other row nicely placed at column F.
 
Last edited:
Unable to replicate this issue. I run code and all checkboxes are aligned on all sheets. Perhaps there is some data already in the first cell?
 
Back
Top