Inserting rows & columns without ruining my macro code.

  • Hi everyone,


    I'm using the following code to automatically make the data in a column uppercase:

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    3. Application.EnableEvents = False
    4. Target = UCase(Target)
    5. Application.EnableEvents = True
    6. End Sub


    I'm having trouble inserting rows & columns (by right clicking on the far left or top & selecting 'Insert') into my spreadsheet without ruining the code for the following cells.

    This has been an issue with both blank & copied inserts.


    When inserting rows the following error message is prompted:



    However, when inserting columns I have noticed that no error message is prompted, but the code is still ruined for the following cells.


    When the 'Debug' option is selected the code looks like this:


    I have also saved the spreadsheet as a macro-enabled file.


    Not sure if I should be using a different code or not.


    Thank you in advance for your help!

  • The problem you are having is caused because you have made the code only work from column I, if you add columns to the left then it won't work.


    This line doesn't make any sense because Target is a Range, so you get the mismatch error, because the code is expecting a value. You need to attach an example of your workbook and explain what you are doing.

  • The problem you are having is caused because you have made the code only work from column I, if you add columns to the left then it won't work.


    This line doesn't make any sense because Target is a Range, so you get the mismatch error, because the code is expecting a value. You need to attach an example of your workbook and explain what you are doing.

    royUK


    Here's the example workbook you suggested posting.



    Thank you!

  • Hi to all.

    Try adding this line of code just before you discriminate range "I:I"; might be enough to solve your criticalissue:

    If Target.Cells.Count > 1 Then Exit Sub


    rollis13


    Tried this one just now but didn't work, although its likely that I did something wrong somehow or am missing a step.


    Thank you!

  • Did you modified the macro with my suggestion like this:

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Cells.Count > 1 Then Exit Sub '<= added
    3. If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    4. Application.EnableEvents = False
    5. ...