CODEWITHSUNDEEP
excelvba
Julien Ganis
Julien Ganis

Reputation: 81

How can I automatically convert a field in lowercase to uppercase in Excel?

I have produced some VBA code to resolve this problem :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   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 ?

Upvotes: 1

Views: 2151

Answers (2)

user857521
user857521

Reputation:

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

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    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....

Upvotes: 1

Matt Klein
Matt Klein

Reputation: 8424

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.

Upvotes: 0

Related Questions