mardi 20 janvier 2015

VBA uncheck checkboxes in a range of cells

Im working on a project checklist for work. Im very new to VBA, but Im trying to learn. My knowledge so far is to google codes and copy-paste. I managed to get somewhat close to my goal. I have created checkboxes that generates todays date in a cell 84 columns to the right. What I would like to do is to insert a new row on row 10. Copy the checkboxes from row 11 below, link and set the checkbox functions...so far so good. However, now I would like to reset all the checkboxes to unchecked in range L10:Y10 so that the dates linked from below row 11 disappears.

Here´s my code for the link and set up if its needed.



Option Explicit
Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 62 'number of columns to the right for link

For Each chk In ActiveSheet.CheckBoxes
With chk
.LinkedCell = _
.TopLeftCell.Offset(0, lCol).Address
End With
Next chk

End Sub

Sub CheckBoxDate()
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 = 84 '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


Sub SetCheckBoxesMacro()
Dim chk As CheckBox

For Each chk In ActiveSheet.CheckBoxes
chk.OnAction = "CheckBoxDate"
Next chk

End Sub


`





Aucun commentaire:

Enregistrer un commentaire