How do I automatically add checkbox or toggle button to Excel 2007 row when adding new row?

Posted on 2011-04-21
Last Modified: 2012-05-11
I have an Excel 2007 workbook with a Status column.  I would like to automatically add a checkbox or toggle button under that column for each row as a new row is added.  If the box is checked, I would like its caption to be "Enabled" and if unchecked "Disabled."  As an alternative, could have a toggle button automatically added when data is added to a new row that can be toggled between "Enabled" and "Disabled."
Question by:KenWeak
    LVL 41

    Accepted Solution

    This macro, pasted in your sheet code page, will add checkbox on row insertion and update Caption as desired.  The Status Column as you didn't specify is column G - see code as that's changeable.

    THe worksheet change checks on row insertion and the worksheet selection change event updates Enabled/Disabled based on checkbox value


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cbObject As New OLEObject
    Dim WS As Worksheet
    Dim cbRange As Range
        If Target.Address = Target.EntireRow.Address Then 'new row added
            Set WS = ActiveSheet
            Set cbRange = WS.Cells(Target.Row, "G")
            Debug.Print cbRange.Address
            Set cbTemp = WS.OLEObjects.Add(ClassType:="Forms.CheckBox.1")
            'Set cbTemp = WS.OLEObjects("CheckBox1")
            With cbTemp
              'show the combobox with the list
              .Visible = True
              .Left = cbRange.Left
              .Top = cbRange.Top
              .Width = cbRange.Width + 5
              .Height = cbRange.Height + 5
              .LinkedCell = cbRange.Address
              .Object.Caption = "Disabled"
              .Object.Value = False
            End With
        End If
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myControl As OLEObject
        For Each myControl In ActiveSheet.OLEObjects
            If Left(myControl.Name, 8) = "CheckBox" Then
                If myControl.Object.Value = True Then
                    myControl.Object.Caption = "Enabled"
                    myControl.Object.Caption = "Disabled"
                End If
            End If
        Next myControl
    End Sub

    Open in new window


    Author Closing Comment

    It worked like a charm!  Thank you so very much for your assistance!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now