vendredi 11 septembre 2015

Specifying a Range of Rows Based on Checkbox

So I have scoured the internet to put together this small macro, but I haven't had any luck figuring out what I'm doing wrong. I know the answer is going to be simple, but it certainly beyond me. The goal is to have a checkbox insert several rows from a separate sheet, then delete those same rows if you uncheck the box. The copy and insert functions are working perfectly, but I do not know how to write the line for the range of rows to delete. The Code below is obviously not correct, because rngD is specified for testing. rngD should be the 7 rows below the check box, or maybe there is a better way to do it. Thanks a lot for checking this out, and I'm open to any constructive criticism.

Working in Excel 2013

Sub Insert_LL()


Dim ws As Worksheet
Dim chkB As CheckBox
Dim rngA As Range
Dim rngD As Range
Dim lRow As Long
Dim lRowD As Long
Dim llRows As Range


Set llRows = Range("Lesson_Learned")
Set ws = ActiveSheet
Set chkB = ws.CheckBoxes(Application.Caller)
lRow = chkB.TopLeftCell.Offset(1).Row
lRowD = chkB.TopLeftCell.Offset(7).Row
Set rngA = ws.Rows(lRow)
Set rngD = ws.Rows("18:24") 'needs to specify the range of rows dependant where the checkbox is located'


Application.ScreenUpdating = False


    Select Case chkB.Value
        Case 1
            llRows.Copy
            rngA.Insert Shift:=xlDown
        Case Else
            rngD.Delete
    End Select

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire