• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto-Transporting Row To Another Sheet If Checkbox is Checked Difficulties

Deity

New Member
Hello there! Firstly, I am not Excel proficient nor entirely VBA literate and although I have progressed thus far, I am unable to complete the sheet. Any assistance would be appreciated. Apologies in advance for any misuse of terminology or coding language. I hope I can provide you with enough information about my problem and am open to engage in any conversation or query.

For context, my worksheet currently is:
Userform inputs to Sheets 2, 4 or 5 depending on a combobox selection and is then autosorted, a checkbox is then required to be ticked in order to submit the row of information from that sheet to a final sheet.
Everything else is functioning except moving specific information from a row (Nothing too complex, just everything but the tickbox) to another sheet if the row's tickbox is ticked.
The tickboxes (Or checkboxes) are Form Checkboxes and not the ActiveX alternatives.

For reference;
Sheet1 = Button to activate Userform.
Sheet2, 4 and 5 = Output sheets from the userform.
Sheet3 = Output for sheets 2,4 and 5.
(Sheet1 > Sheet2 or 4 or 5 > Sheet 3)

The problems I encounter are:
- I do not want the entire row to be selected to move/ deleted due to the checkboxes.
- Determining where to place the information on Sheet3 from Sheets2, 4 or 5
- Ensuring auto-reset of the checkboxes.
- Ensuring correct row (Row that has its checkbox checked) is selected and deleted after transportation
- Ensuring correct row is transported.

Is this even the correct method of accomplishing this function? Should I be using a drop-down list instead and then comparing those values? Or is there a more convenient alternative?

Through research, I have obtained and adapted the following code. The Clearcheck sub works perfectly fine. The idea is that once the specific information in the row (From Sheet2, 4 or 5) is moved to Sheet3, the checkboxes reset (To await additional information)

B4 of Sheet3 is where I want the information to initially transfer to (As my headings and such occupy 1 - 3 and A is occupied by other things) and then to transfer below eachother.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet3.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet2.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is appropriate value
     If Is(TRUE) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
        Application.EnableEvents = False
        Target.EntireRow.Select
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.Delete
' Reset EnableEvents
        Application.EnableEvents = True
    End If
End If

Call clearcheck

End Sub

Sub clearcheck()
    Dim sh As Worksheet
    For Each sh In Sheets
        On Error Resume Next
            sh.CheckBoxes.Value = False
        On Error GoTo 0
    Next sh
End Sub
 
Last edited:
Hi,

Welcome to the forum ;)

Can you please upload a sample file with some dummy data... it is easier to give a targeted answer that way.

Thanks
 
Here is my current worksheet. I have labelled everything as best as I can to try and allow a better understanding of what my worksheet does.
 

Attachments

  • DummySheet.xlsm
    565.6 KB · Views: 33
Additionally, there is another formula on the worksheet that is not functioning (Possibly due to the same error with the initial formula) which can be found in Sheet3(FinishedJob). This formula is intended to regulate (Delete) information based on current date vs date inputted. I have managed to freeze output from the now() function to produce the "date inputted"
Formula used to freeze now() output:=IF(B20<>"",IF(H20="",NOW(),H20),"")

Code:
Sub delete_old()

    Dim rowDate
    Dim curDate
    Dim interval
    Dim curAdd
    Dim vCell As Range

    ' set interval to an appropriate number of days
    interval = 21 ' can be more precise e.g. for 5 mins: (1 / 24) / 12
    curDate = Now()

    ' assuming we want to start from row 4 and that date is in column H
    Set vCell = Range("H4")

    ' Set Do loop to stop when an empty cell is reached.
    Do Until IsEmpty("H4")
        curAdd = vCell.Address
        If curDate - vCell.DateValue >= interval Then
            vCell.EntireRow.Delete
            Set vCell = Range(curAdd)
        Else
            Set vCell = Range(curAdd).Offset(1, 0) ' next row
        End If
    Loop

    Set vCell = Nothing

End Sub

I believe it is perhaps my ranges that require amelioration, but I am unsure if this is the case and in which way to adjust them. Several debug error codes I have received are as follows: 424, 438, 13, 1004, 5.

Any assistance would be greatly appreciated. The worksheet can be accessed in my previous reply for reference.
 
The following code seems to function perfectly, although results in an Error 13: Type mismatch. Could anyone advise on where the data types conflict?

Code:
Sub delete_old()

    Dim rowDate
    Dim curDate
    Dim interval
    Dim curAdd
    Dim vCell As Range

    ' set interval to an appropriate number of days
    interval = (1 / 24) / 12 ' can be more precise e.g. for 5 mins: (1 / 24) / 12
    curDate = Now()

    ' assuming we want to start from row 4 and that date is in column H
    Set vCell = Range("H4")

    ' Set Do loop to stop when an empty cell is reached.
    Do Until IsEmpty(vCell)
        curAdd = vCell.Address
        If curDate - vCell.Value >= interval Then
            vCell.EntireRow.Delete
            Set vCell = Range(curAdd)
        Else
            Set vCell = Range(curAdd).Offset(1, 0) ' next row
        End If
    Loop

    Set vCell = Nothing

End Sub
 
Here is my current worksheet. I have labelled everything as best as I can to try and allow a better understanding of what my worksheet does.
Hi,

About moving the row's content to the "FinishedJob" sheet, you can use
Code:
Sub MoveToFinished()

    Dim R, lrow As Integer
   
    R = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    lrow = Sheets("FinishedJob").Cells(Rows.Count, "B").End(xlUp).Row + 1

    Range("A" & R & ":G" & R).Cut Sheets("FinishedJob").Cells(lrow, 1)
    ActiveSheet.Shapes(Application.Caller).ControlFormat.Value = 0

End Sub

You need to assign this macro to all the checkboxes.
When checked, the contents from A:G of that specific row will be moved over and the checkbox will be unchecked.

Hope this helps
 
Thanks! Works flawlessly, except that the freeze date formula (=IF(B20<>"",IF(H20="",NOW(),H20),"")) outputs as #REF! and I'm not entirely sure as to why. After checking https://support.office.com/en-gb/ar...EF-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be
it appears to be due to the formula's explicit cell referencing, I'm just not at all sure how to achieve the formula's effect if I try to adjust it. Is there any way around this?

Also, the formula cells in Column A of Sheets2, 4 and 5 are left as e.g:"=FinishedJob!E29-B$2" instead of "=F4-B$2" which then does not update once more information is input. I assume this is due to the cut function but yet again, I know of no way to work around this in this instance.

Additionally, and rather strangely; The marco leaves the "Service Order Number" column as a date format, which then distorts incoming data.

Edit: A way I've gotten around this is to use the following recorded macros and then call them from your Subroutine:
Code:
Sub RefreshFormulas()
'
' RefreshFormulas Macro
' Drags Formulas in Column A to reset them
'

'
    ActiveWindow.SmallScroll Down:=105
    Range("A121").Select
    Selection.AutoFill Destination:=Range("A4:A121"), Type:=xlFillDefault
    Range("A4:A121").Select
End Sub

Sub ReFormatColumn()
'
' ReFormatColumn Macro
' Reformats the column back to general
'

'
    Range("B4:B121").Select
    Selection.NumberFormat = "General"
End Sub

Would you advise the use of recorded macros here?
 
Last edited:
Hello again Hui! I've managed to fix the sheet by cheating a little (Recorded Macros) But now it is fully functional. Thanks a bunch for your help! Hope this helps someone who is in the same situation. Here's the adapted code and the modules that reset things:

Edit: After an hour or so of bug testing a major flaw has become apparent: Re-applying the formula in the FinishedJobs sheet just re-applies the formula to everything and thus everything has the same "Date Completed" as the looped formula just updates everything as the current time.

Code:
Sub MoveToFinished()

    Dim R, lrow As Integer
 
    R = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    lrow = Sheets("FinishedJob").Cells(Rows.Count, "A").End(xlUp).Row + 1

    Range("B" & R & ":G" & R).Cut Sheets("FinishedJob").Cells(lrow, 1)
    ActiveSheet.Shapes(Application.Caller).ControlFormat.Value = 0
 
    Call RefreshFormulas
 
    Call ReFormatColumn
   'I make this active in order for the recorded macro to function
    Worksheets("FinishedJob").Activate

Call ResetFormula

End Sub

'These are the recorded macros
Sub RefreshFormulas()
'
' RefreshFormulas Macro
' Drags Formulas in Column A to reset them
'

'
    ActiveWindow.SmallScroll Down:=105
    Range("A121").Select
    Selection.AutoFill Destination:=Range("A4:A121"), Type:=xlFillDefault
    Range("A4:A121").Select
End Sub
Sub ReFormatColumn()
'
' ReFormatColumn Macro
' Reformats the column back to general
'

'
    Range("B4:B121").Select
    Selection.NumberFormat = "General"
End Sub
Sub ResetFormula()
'
' ResetFormula Macro
' Resets the freeze date formula
'

'
    ActiveWindow.SmallScroll Down:=990
    Range("G999").Select
    Selection.AutoFill Destination:=Range("G4:G999"), Type:=xlFillDefault
    Range("G4:G999").Select
End Sub
 
Last edited:
Hi,

Instead of moving you could copy/paste as values and then simply delete the originals from the source sheet (2,4 and 5)... that way, the formulas will remain intact (that was my mistake, should have noticed that right away) and will also solve the format as date problem, since the cells are not being moved.

It is a very simple change in the code... If you need help with that, please let me know

About the formulas for the completed date in the "FinishedJob" sheet, how exactly does that work... should it freeze the date as soon as it is calculated?
If so, and as an example, you can have some code convert the formulas to values as soon as the cells to the left are populated.
Does that work for you?

Recorded macros are ok in most cases. The thing with recorded macros is that they record every single thing you do (the scrolling for instance) and can, in many cases, be further optimized.

In your case:
"ActiveWindow.SmallScroll Down:=105";
"Range("A121").Select";
as well as "Range("A4:A121").Select"
can all be deleted

As a matter of fact, avoiding ".select" whenever possible is always a good practice.

As for the current time problem, if the dates are stored as values as soon as they are calculated, that should not be an issue either.

If you want, upload the updated sample file and I will gladly try to help fix these issues.
 
Instead of moving you could copy/paste as values and then simply delete the originals from the source sheet (2,4 and 5)... that way, the formulas will remain intact (that was my mistake, should have noticed that right away) and will also solve the format as date problem, since the cells are not being moved.

I have no idea how to do this and any assistance would be greatly appreciated. Ah, so the moving of the cells is what caused the REF! and the format errors, good to know!

About the formulas for the completed date in the "FinishedJob" sheet, how exactly does that work... should it freeze the date as soon as it is calculated?
If so, and as an example, you can have some code convert the formulas to values as soon as the cells to the left are populated.
Does that work for you?

The idea is is that the date should freeze whenever information arrives onto the sheet. Almost like some sort of booby-trap. Information occupies A and then the date freezes to show when the information was sent to that sheet for future reference.

Recorded macros are ok in most cases. The thing with recorded macros is that they record every single thing you do (the scrolling for instance) and can, in many cases, be further optimized.
In your case:
"ActiveWindow.SmallScroll Down:=105";
"Range("A121").Select";
as well as "Range("A4:A121").Select"
can all be deleted
That makes total sense, thanks, deleted.

As a matter of fact, avoiding ".select" whenever possible is always a good practice.
I shall try to avoid doing so as much as possible.

As for the current time problem, if the dates are stored as values as soon as they are calculated, that should not be an issue either.

I believe this is exactly what I want to achieve. Information is ouput onto the sheet and then the date freezes for that row never to be altered again (Until the delete_old subroutine launches and deletes things over 200 days old) (Just noticed the delete_old subroutine may cause data to "float" after it deletes some things and leaves gaps between others)

If you want, upload the updated sample file and I will gladly try to help fix these issues.

Please find attached the most recent incarnation of the worksheet.

I hope I am able to articulate my intentions concisely and must apologise where I fail to do so. Thank you for your time and assistance.
 

Attachments

  • Workshop Progress Spreadsheet.xlsm
    573.3 KB · Views: 23
Hi,

Please refer to attachment.

I deleted the formulas in "FinishedJob" and used "NOW()" in VBA to insert the date into the column "H" when moving the data over from the other sheets. It is a better approach in my opinion.
There's no need for the current date or current date and time on the top left ("A1:B2")... everything is handled via VBA. You can delete those if you have no further use for them.

Also, I changed
Code:
vCell.EntireRow.Delete

to
Code:
vCell.EntireRow.Delete Shift:=xlUp
in the "Delete_old" sub... this way it will delete the row and move the rows below up, to fill the gaps.

Please test it out and let me know if there is anything else I can help with.
 

Attachments

  • Workshop Progress Spreadsheet.xlsm
    552.1 KB · Views: 53
By the way... I noticed that, when using the form, "Date received" and "Date required" seem to be placed incorrectly in the respective sheets.

Cheers
 
Please test it out and let me know if there is anything else I can help with.

Upon frequent testing it works perfectly. Absolutely as intended! I am amazed to how it functions so well with so much less code.

There's no need for the current date or current date and time on the top left ("A1:B2")... everything is handled via VBA.

It's just sort of a quick visual reference for comparing dates for the user rather than having to glance down at the calendar, it's not really necessary. But thanks for the heads up!

"Date received" and "Date required" seem to be placed incorrectly in the respective sheets.

You are entirely correct. This is not intentional. (Haha) I have swapped the names and adjusted the formula(s) to now calculate the appropriate column.

Also, it inserts a new row which doesn't have any checkbox... i suspect this behavior is probably not what you were aiming for since it will need the user to add a checkbox to the line and assign the macro for moving the info over to the finished sheet.

This behaviour is certainly unintentional and not at all desired. I seem to have completely overlooked that and am not sure how to adjust it so that it does not happen.

Once again; it functions exactly as intended, with seemingly no errors (That I can see)

Thanks again!
 
This behaviour is certainly unintentional and not at all desired. I seem to have completely overlooked that and am not sure how to adjust it so that it does not happen.

You may have noticed that I erased that part from my previous post as I couldn't replicate the problem after posting (you caught that before I could erase it, sorry for that)... it must have been something I did and not a problem with the code.

I will continue trying to replicate it and if I find any problem I will post it here... appreciate it if you could do the same :)

Cheers
 
You may have noticed that I erased that part from my previous post as I couldn't replicate the problem after posting (you caught that before I could erase it, sorry for that)... it must have been something I did and not a problem with the code.
I will continue trying to replicate it and if I find any problem I will post it here... appreciate it if you could do the same :)

I will admit I was a little confused initially upon reading that as I have not encountered that specific problem at all (Or any other).

I have been testing the sheet relentlessly in every (albeit limited) way(s) I know how and have not encountered the problem you so kindly illustrated.

The sheet is working exactly as intended thanks to you. Thank you very much for your assistance. I also hope our dialogue assists anyone else encountering similar issues. Have a great day.
 
I will admit I was a little confused initially upon reading that as I have not encountered that specific problem at all (Or any other).

I have been testing the sheet relentlessly in every (albeit limited) way(s) I know how and have not encountered the problem you so kindly illustrated.

The sheet is working exactly as intended thanks to you. Thank you very much for your assistance. I also hope our dialogue assists anyone else encountering similar issues. Have a great day.
You are welcome, glad I could help ;)
 
Back
Top