Change to UPPER Case

[Steve 1962]

Hi

Could someone tell me what VBA code to use in order to have all entries in cells A3 to A5003 be automatically entered as UPPER case ?

Thanks

SH

 

[Papi]

Sorry...using the quoted code...

Try this: right click the sheet tab, select View Code and paste in

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

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3:C5000")) Is Nothing And Target.Offset(, 1).Value = "Client" Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
End If
End Sub

 

[Papi]

It works fine ie. if I enter each cell with F2 and hit enter it converts all the existing clients to upper case. However, I run two macros to insert a row for a new contact or one that is existng i.e. Client or Supplier etc. and it gives me an eror on the first bit of code. One macro enters all new blank cells while the other duplicates an existing company and leaves information like company name, address etc and removes such things as names and personal information.

 

[Papi]

Also, the column for the client etc is in D and the name of the client is in C. I see it is in the wrong position and the form is used by several departments so cannot move it to show before the client name... Does this knock this out of the question?

 

[Papi]

Hello Peter,

I took a look at where all the workbooks go to and believe that I can reverse so that the Contact type i.e. Client, Employee etc can come before the company name i.e. reverse D with C. Does that help?

 

[VoG]

Perhaps

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D3:D5000")) Is Nothing And Target.Offset(, -1).Value = "Client" Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
End If
End Sub

 

[Papi]

Thanks a lot Peter. The code works fine but it continues to get a run-time error 1004 every time a row is added or deleted and returns Application-defined or object-defined error.

 

[VoG]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D3:D5000")) Is Nothing And Target.Offset(, -1).Value = "Client" Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
End If
End Sub

 

[Papi]

Thanks very much Peter! That works perfectly! I really appreciate all your help on this.