Hi,
I've got checkbox in a worksheet called 'Checklist' and I am using the below code to copy the data in the cells next to the checkboxes when selected into another worksheet named 'Estimate'. The pasted cells are to start from B12. I need to be able to check multiple boxes and have them paste in the next available cells from B12, at the moment they just paste over the top of the current data in B12. I also would like the data cleared when the boxes are deselected, the only code I had figured out clears everything in the worksheet from B12, but I have info in various other columns and rows. I've attached my workbook, please help!!!
Module:
Sub Chbx()
Dim shp As Shape, ws As Worksheet, msg As String, c As Integer
Dim cop
c = 1
With Sheets("Estimate")
cop = .Rows(1).Value
.Rows(1).Value = cop
End With
For Each ws In ActiveWorkbook.Worksheets
If Not ws.Name = "Estimate" Then
For Each shp In ws.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlCheckBox Then
If shp.ControlFormat.Value = 1 Then
c = c + 11
ws.Range(shp.ControlFormat.LinkedCell).Offset(, 1).Resize(, 1).Copy Sheets("Estimate").Cells(c, 2)
End If
End If
End If
Next shp
End If
Next ws
End Sub
Checkbox macros (for each checkbox):
Sub CheckBox3_Click()
Call Chbx
End Sub
Bookmarks