ShammoAero
New Member
- Joined
- Aug 31, 2017
- Messages
- 3
Hi everyone,
I'm fairly new to VBA so this doesn't seem obvious to me at the moment.
I've managed to use Checkboxes to copy the rows that have a check mark from sheet1 to sheet2.
The problem is that if I insert a new row in sheet1 somewhere in the middle of the worksheet and I insert a checkbox into that row which I then check mark, the row will be inserted as the LAST ROW of sheet2. I need the row to be inserted in the same order as they appear in sheet1.
Here is the code that I am currently using:
Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
Application.ScreenUpdating = False
LRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For cell = 3 To LRow
If Cells(cell, "A").Value <> "" Then
CLeft = Cells(cell, "E").Left
CTop = Cells(cell, "E").Top
CHeight = Cells(cell, "E").Height
CWidth = Cells(cell, "E").Width
ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
With Selection
.Caption = ""
.Value = xlOff
.Display3DShading = False
End With
End If
Next cell
Application.ScreenUpdating = True
End Sub
Sub RemoveCheckboxes()
Dim chkbx As CheckBox
For Each chkbx In ActiveSheet.CheckBoxes
chkbx.Delete
Next
End Sub
Sub CopyRows()
Sheets("sheet2").Range("A2:TX100000").ClearContents 'This ensures that entries aren't duplicated
For Each chkbx In ActiveSheet.CheckBoxes
If chkbx.Value = 1 Then
For r = 1 To Rows.Count
If Cells(r, 1).Top = chkbx.Top Then
With Worksheets("C.I.D Summary")
LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & LRow & ":S" & LRow) = _
Worksheets("sheet1").Range("A" & r & ":S" & r).Value
End With
Exit For
End If
Next r
End If
Next
End Sub
This works well, until I need to add another row somewhere in the middle of sheet 1, which has a check mark. Then the row will be added at the end of sheet 2, instead of the order in which it appears in sheet1. The problem lies with the fact that Checkboxes are given an I.D which is in order of when they were placed in the sheet. If I remove all the checkboxes using the RemoveCheckboxes subroutine, then add them again using AddCheckboxes, then this will "solve" this problem but all the checkboxes will be cleared, and I have about 1000 checkboxes that will need to be checked again manually.
Does anyone have a solution to this problem?
Thank you very much.
I'm fairly new to VBA so this doesn't seem obvious to me at the moment.
I've managed to use Checkboxes to copy the rows that have a check mark from sheet1 to sheet2.
The problem is that if I insert a new row in sheet1 somewhere in the middle of the worksheet and I insert a checkbox into that row which I then check mark, the row will be inserted as the LAST ROW of sheet2. I need the row to be inserted in the same order as they appear in sheet1.
Here is the code that I am currently using:
Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
Application.ScreenUpdating = False
LRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For cell = 3 To LRow
If Cells(cell, "A").Value <> "" Then
CLeft = Cells(cell, "E").Left
CTop = Cells(cell, "E").Top
CHeight = Cells(cell, "E").Height
CWidth = Cells(cell, "E").Width
ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
With Selection
.Caption = ""
.Value = xlOff
.Display3DShading = False
End With
End If
Next cell
Application.ScreenUpdating = True
End Sub
Sub RemoveCheckboxes()
Dim chkbx As CheckBox
For Each chkbx In ActiveSheet.CheckBoxes
chkbx.Delete
Next
End Sub
Sub CopyRows()
Sheets("sheet2").Range("A2:TX100000").ClearContents 'This ensures that entries aren't duplicated
For Each chkbx In ActiveSheet.CheckBoxes
If chkbx.Value = 1 Then
For r = 1 To Rows.Count
If Cells(r, 1).Top = chkbx.Top Then
With Worksheets("C.I.D Summary")
LRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & LRow & ":S" & LRow) = _
Worksheets("sheet1").Range("A" & r & ":S" & r).Value
End With
Exit For
End If
Next r
End If
Next
End Sub
This works well, until I need to add another row somewhere in the middle of sheet 1, which has a check mark. Then the row will be added at the end of sheet 2, instead of the order in which it appears in sheet1. The problem lies with the fact that Checkboxes are given an I.D which is in order of when they were placed in the sheet. If I remove all the checkboxes using the RemoveCheckboxes subroutine, then add them again using AddCheckboxes, then this will "solve" this problem but all the checkboxes will be cleared, and I have about 1000 checkboxes that will need to be checked again manually.
Does anyone have a solution to this problem?
Thank you very much.
