1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by ladydee, Jun 19, 2017 at 5:24 AM.

  1. ladydee

    ladydee New Member

    Messages:
    14
    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.
  2. AlanSidman

    AlanSidman Member

    Messages:
    85
    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.
  3. ladydee

    ladydee New Member

    Messages:
    14
    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.
  4. AlanSidman

    AlanSidman Member

    Messages:
    85
    Looks at Column A for data to determine last row.
    Code (vb):

    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
     
  5. ladydee

    ladydee New Member

    Messages:
    14
    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?
  6. AlanSidman

    AlanSidman Member

    Messages:
    85
    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.
  7. AlanSidman

    AlanSidman Member

    Messages:
    85
    Amended

    Code (vb):
    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

     
  8. ladydee

    ladydee New Member

    Messages:
    14
    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
  9. AlanSidman

    AlanSidman Member

    Messages:
    85
    Fixed> See Below

    Code (vb):
    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

     
  10. ladydee

    ladydee New Member

    Messages:
    14
    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: Jun 21, 2017 at 3:14 AM
  11. AlanSidman

    AlanSidman Member

    Messages:
    85
    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?
  12. ladydee

    ladydee New Member

    Messages:
    14
    sorry, do you means first cell in column F?
  13. AlanSidman

    AlanSidman Member

    Messages:
    85
    Yes. If I understand that is the cell that you are having issues with. I cannot replicate the issue.

Share This Page