[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-04-21
Medium Priority
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 42

Accepted Solution

dlmille earned 2000 total points
ID: 35443061
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

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

826 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