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:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
 Application.EnableEvents = False
 Target = UCase(Target)
 Application.EnableEvents = True
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!

Quote from royUK

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!

Quote from rollis13

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!

Quote from royUK

Try using this line

Code

If Not IsEmpty(Target) Then Target.Value = UCase(Target.Value)

Just tried it but had no luck

Thank you!