VBA for using a check box to Regen that row and then uncheck the box (ready to run macro again)

[KCobster24]

Hello All,

I'm making myself a task list, in this list i will have repeating tasks. Once the task is done (and dated) it needs to be done again (think how the laundry is in an never ending loop of despair).
I'm VERY new to VBA and despite a few days of google searching (which I, previous to this endeavor, thought I was great at google research) I cannot figure out a code that will accomplish the above.

THUS Far I've been able to do accomplish this for regenerating the line item....... (i'd attach a sheet but the mini-sheet capture range in my add-in is greyed out and won't let me use it for some reason.....):

Sub regenerate_Task()

' regenerate_Task Macro
Rows("2:2").Select
Selection.Copy
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

End Sub

AND This for getting it to generate a date done:.....

Sub CheckBox_Date_Stamp1()
Dim xChk As CheckBox
Set xChk = Sheet2.CheckBoxes(Application.Caller)
With xChk.TopLeftCell.Offset(, 7)
If xChk.Value = xlOff Then
.Value = ""
Else
.Value = Date
End If

End With
End Sub

However I can't get the box to be pasted unchecked (so its ready to be used later) and I've not even added in the dating part that I've figured out on a different sheet (I had planned on combining the codes at the end). I've tried various things but ended up cutting them from the code above since I'd rather have a partly working code than a fully broken one.

In a Perfect world I'd have the checkbox do the following things:

Copy the line item
Populate the date completed in a "Date Done" column (no preference what column this gets assigned to)
Paste the task (the row we just checked) with an unchecked box to the last row (I know my code above just inserts below, i was also struggling with last row issues so i compromised)

Any instruction, tips, tricks, full code for me to copy paste, etc. is welcome. TYSMIA

 

[Micron]

Those subs cannot call themselves so what would you be using to make them run? Code should probably be in the click events for the checkboxes?
Are you saying that you cannot copy a range from your sheet and paste in a post?
Based on your ideas:
- I'd say it's common to get the row number of the clicked checkbox and do whatever after that. However you don't have to select a row to copy and insert.
- The 'label' portion is often taller than the box itself. If it protrudes into the row above it presents an issue for the above point. It might not today, but resizing rows can mess that up. If that can happen, I think it's possible to check if the checkbox is taller than the row it's in and prevent that.
- It's very common to get the last row on a sheet but one has to know what to base that on. F'rinstance if col A will always have values in a row (but B might not) then it's easy enough to base it on A. If the last row can be anywhere, a different approach is needed.
- how is it determined what date is the "Date Done"?