CODEWITHSUNDEEP
vbaexcel
tbowden
tbowden

Reputation: 1058

Check Boxes in Excel

I am trying to insert multiple Check Boxes (100+), but don't want to have to individually add them. Is there a quick way of doing this? I have tried the pull down method, and it does indeed make more, however they are all connected up - the same happens with copy and paste! When 1 is clicked they all are!

The macro that is fired when the tick box is clicked is as follows, I wonder if it can be altered?

Sub tick()

Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD As Long
Dim lColChk As Long
Dim lRow As Long
Dim rngD As Range
lColD = 3 'number of columns to the right for date

Set ws = ActiveSheet
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)

Select Case chk.Value
   Case 1   'box is checked
      rngD.Value = Date
   Case Else   'box is not checked
      rngD.ClearContents
End Select

End Sub

Upvotes: 0

Views: 265

Answers (2)

Trum
Trum

Reputation: 630

This is a code I use quite frequently. If you select a range and run this macro - it will add a checkbox for every selected cell and tie it to that cells value (boolean)

I don't use captions - but you can easily modify it if you would like.

I hope that this helps

Sub Add_checkboxes()


Application.ScreenUpdating = False

    For Each the_cell In Selection
    ActiveSheet.CheckBoxes.Add(Range(the_cell.Address).Left, Range(the_cell.Address).top, 18, 12).Select
    With Selection
        .Caption = ""
        .Value = xlOff '
        .LinkedCell = the_cell.Address
        .Display3DShading = False
    End With
    Next

Application.ScreenUpdating = True

End Sub

Upvotes: 2

psychicebola
psychicebola

Reputation: 949

Add this code to the Sheet-Module in VBA and mark the columns you want to use as checkboxes with "Selection" (just an example). This procedure marks the specified cells with an "X" when double-clicked

Private Sub Worksheet_beforedoubleClick(ByVal Target As Range, Cancel As Boolean)
    'If row 6 of a cell contains "Selection" all rows below are used as checkboxes.    
    If Me.Cells(6, Target.Column) = "Selection" And Target.Row > 6 Then

    'Function to mark the field with an "x"

          If Target.Value = "x" Then
              Target.Value = ""
          Else
              Target.Value = "x"
          End If

    End If
End Sub

Upvotes: 1

Related Questions