• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

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

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."
0
KenWeak
Asked:
KenWeak
1 Solution
 
dlmilleCommented:
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

Enjoy!

Dave
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"
            Else
                myControl.Object.Caption = "Disabled"
            End If
        End If
    Next myControl
End Sub

Open in new window

DynamicCheckboxOnRowAdd-r1.xlsm
0
 
KenWeakAuthor Commented:
It worked like a charm!  Thank you so very much for your assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now