Take the 2-minute tour ×

I'm currently trying to write a macro based on sheet change, where the letters in a table column are automatically converted to upper case. So, for example, if I entered "abcde-12345-678" into a cell, it would automatically correct to "ABCDE-12345-678". After doing some digging, I found some code that has worked for some people, but I'm having trouble tweaking it to suit my needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

There are two things that I would like to address. The first being, that this code isn't currently working for me. I have it in the correct location according to the author (located in the Sheet1 object). Are there any ideas as to why this isn't working?

The second is that I would like to modify the code to refer to a table column rather than a range. For example, I've tried changing the second line of the above code to the following (the name of my table is ReviewTracker, and the column I'm interested in is Product Number):

If Intersect(Target, Range(ReviewTracker[[@Headers],[Product Number]])) Is Nothing Then Exit Sub

This returned a compile error "Expected: list separator or )". So there is obviously something wrong with it, but hopefully it might help illustrate what it is I'm trying to accomplish.

Thanks in advance for any help on the issue.


share|improve this question
to check if it works- add the following line Debug.Print Now as the first line inside, just before If Intersect.... Put some value in E:E column and go back to VBA Editor. In immediate window you should have some inputs with current date and time. Is there anything? –  KazJaw Oct 21 '13 at 14:28
The code works fine in column E. Why would you need to use the table column if you can simply use range("A2:A10") for example –  mehow Oct 21 '13 at 14:32
add comment

2 Answers

up vote 3 down vote accepted

First. You can have events disabled due to lots of reason. Let's make it sure that events are on which you can do as follows:

go to VBA Editor >> open Immediate Window >> write there: Application.EnableEvents = true >> press Enter

Second. To check if intersection match appropriate column within you ListObject table you need something like this:

If Intersect(Target, Range("ReviewTracker[Product Number]")) is Nothing Then

assuming that ReviewTracker is table name and Product Number is table column. You don't need #Headersas it will refer only to header row.

share|improve this answer
+1 for the Range("ReviewTracker[Product Number]") bit. –  varocarbas Oct 21 '13 at 14:46
Thanks for the quick response. This is exactly what I was looking for. –  detroitwilly Oct 21 '13 at 14:59
add comment

What UCase does is converting all the characters in a given string into upper case and thus you can apply it to any Range.Value. Worksheet_Change is called every time the value of a cell has changed and thus is a good place to put your code. But the way you are using to refer the table is wrong. The code your posted adapted to your requirements:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Sheet1.ListObjects("Table1").ListColumns(1).Range) Is Nothing Then Exit Sub
    Target.Value = UCase(Target.Value)
End Sub

It converts into upper caps any string input in the first column of Table1 in Sheet1. It has to be placed in the Sheet1 object file (in the VBA Project explorer: Sheet1 (Sheet1) inside the Microsoft Excel Object folder). Adapting it to your actual conditions is straightforward.

share|improve this answer
+1 ...30 seconds before me :) –  KazJaw Oct 21 '13 at 14:48
add comment

Your Answer


