1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Deity, Jan 25, 2017.

  1. Deity

    Deity New Member

    Messages:
    9
    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 (vb):
    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: Jan 25, 2017
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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
  3. Deity

    Deity New Member

    Messages:
    9
    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.

    Attached Files:

  4. Deity

    Deity New Member

    Messages:
    9
    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 (vb):

    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.
  5. Deity

    Deity New Member

    Messages:
    9
    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 (vb):


    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


     
  6. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi,

    About moving the row's content to the "FinishedJob" sheet, you can use
    Code (vb):
    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
    Deity and Thomas Kuriakose like this.
  7. Deity

    Deity New Member

    Messages:
    9
    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 (vb):
     
    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: Jan 31, 2017
  8. Deity

    Deity New Member

    Messages:
    9
    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 (vb):

    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: Jan 31, 2017
  9. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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.
  10. Deity

    Deity New Member

    Messages:
    9
    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!

    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.

    That makes total sense, thanks, deleted.

    I shall try to avoid doing so as much as possible.

    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)

    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.

    Attached Files:

  11. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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 (vb):
    vCell.EntireRow.Delete
    to
    Code (vb):
    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.

    Attached Files:

  12. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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
  13. Deity

    Deity New Member

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

    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!

    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.

    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!
  14. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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
  15. Deity

    Deity New Member

    Messages:
    9
    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.
  16. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    You are welcome, glad I could help ;)
    Deity likes this.

Share This Page