Take the 2-minute tour ×

I have produced some VBA code to resolve this problem :

   If Target.Value <> Empty Then
      Target.Value = UCase(Target.Value)
   End If
End Sub

But when I try to input some data in a field, Excel stops working without a single error message.

Does anyone know where this problem can come from ?

share|improve this question
The funny thing is that the code works before Excel stops... –  Julien Ganis Oct 4 '12 at 9:19
Did you set the enableEvents to false? Otherwhise, it would self trigger again (or any other eventHandler). –  CaBieberach Oct 4 '12 at 9:28
add comment

2 Answers

up vote 2 down vote accepted

You probably have set Application.EnableEvents = False. Open the Immediate window in the VBA editor and type in application.EnableEvents = True then ENTER to turn them back on.

Also, you need to disable events if you don't want to cause a cycle of changing the sheet and re-triggering the event. The ISEMPTY function is slightly different in VBA and your code could be updated to the following which will also handle changing more than just 1 cell

Option Explicit

    Dim cell As Variant

        Application.EnableEvents = False
            For Each cell In Target
                If Not IsEmpty(cell.Value) Then
                   cell.Value = UCase(cell.Value)
                End If
           Next cell
        Application.EnableEvents = True
    End Sub

or if you want to restrict this running to 1 cell change only, replace the for each loop with If Target.rows.count = 1 AND Target.columns.count = 1....

share|improve this answer
In the complete code, i did this : If Target.Cells.Count = 1 And (Target.Column = 1 Or Target.Column = 4)... But you might be right, there is probably a cycle of triggering the event, I check this, thanks –  Julien Ganis Oct 4 '12 at 9:48
Aaaaaand, i feel dumb. –  Julien Ganis Oct 4 '12 at 9:57
add comment

You may not have the callback function in the right spot:

From Events And Event Procedures In VBA

For sheet (both worksheet and chart sheet) level events, the event procedure code must be placed in the Sheet module associated with that sheet. Workbook level events must be placed in the ThisWorkbook code module. If an event procedure is not in the proper module, VBA will not be able to find it and the event code will not be executed.

share|improve this answer
Actually it is in the right spot, i put it in the "This Worbook" MSExcel Object so it applies to all the workbook, not only in something like the 1st worksheet. Anyway, I've tried to put it where you said, in a blank Excel project, and it didn't work either –  Julien Ganis Oct 4 '12 at 9:17
add comment

Your Answer


