Posts by CapG

    There is no target cell per say. Because this in the middle of execution the control shifts from primary code to template code.

    Primary code here is a loop having to split one workbook to multiple workbooks and

    each workbook should have incorporated change event macro in it. The template file is used for this purpose.

    below is the primary code, after the line highlighted in yellow executes, the control transfers to template cod

    this code is in a template file so the new workbooks that are created(in a loop) goes through this template file to incorporate event handler in every workbook that it undergoes. It didnt occur for other workbook though. Do I have to convert r to range in global declaration?

    This is what it looks like while stepping into the template code:

    First these two lines were stepped into without error

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.CountLarge > 1 Then Exit Sub

    worksheetChange sub exists since Target.countlarge>1 next the control goes to selectionChange sub. See below screenshot where the overflow error occurs when I hover over r it displays=> r=Empty

    I also used the same code to overcome the error, it worked. Thanks Roy for your help.

    But out of curiosity just wanted to know why did that overflow error occurred only for one file

    this code is in a template file so the new workbooks that are created(in a loop) goes through this template file to incorporate event handler in every workbook that it undergoes. It didnt occur for other workbook though. Do I have to convert r to range in global declaration?

    Like I said the error appears only for one file out of 40 files.

    Yes I agree r cannot be range.

    I tried setting r=target.value(r declared as variant globally) it throws same error saying "Overflow".

    BUT i havent tried declaring r as string explicitly as I thought variant would implicitly convert to particular datatype.

    To my understanding the purpose of r in selection change event is to- when cell value in in range BM-BO is deleted the deletion should reflect in the corresponding row having that header(BM-BO has a data validation list).


    Now when I have used SET r= target this functionality is not working.


    Code
    1. x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(r, Sheets("Key").Range("B1:B20"), 0))
    2. If x >= 1 Then Cells(Target.Row, x + 49).ClearContents
    3. ActiveCell.Offset(1, 0).Select
    4. ActiveCell.Offset(-1, 0).Select

    this code is in a template file so the new workbooks that are created(in a loop) goes through this template file to incorporate event handler in every workbook that it undergoes. It didnt occur for other workbook though. Do I have to convert r to range in global declaration?

    I am really not sure about this codes as I didnt write this. I assume r is used here in this section to delete the corresponding cells when delete key is pressed from range BM to BO

    Code
    1. If r = "" Then Exit Sub
    2. x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(r, Sheets("Key").Range("B1:B20"), 0))
    3. If x >= 1 Then Cells(Target.Row, x + 49).ClearContents
    4. ActiveCell.Offset(1, 0).Select
    5. ActiveCell.Offset(-1, 0).Select
    6. Exit Sub

    getting error for the line

    Code
    1. r = Target

    Here is the script(.vbs) that I' trying to execute to run macro in a specific workbook and I have checked enable macro option in trusted setting. Though I'm getting above error message