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

 

[kpark91]

Hi, Steve

Try.
This is without any error checking so it is up to you to do that.
Moreover, it will only work when a user changes only one cell at a time. (at least I think it will..)

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Value = UCase(CStr(Target.Value))
End Sub

 

[VoG]

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

 

[bjbalmforth]

Try this code, change the range to suit your needs

[Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Change A1:A10 to the range you desire
'Change UCase to LCase to provide for lowercase instead of uppercase

If Not Application.Intersect(Target, Range("A1:L3000")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If

Application.EnableEvents = True
End Sub]

 

[Steve 1962]

Hi Peter (VoG)

I have followed your instructions exactly however, the inputs are not changing case. I'll try again but I'm sure that I am doing everything to the letter.

Steve

 

[VoG]

Hi. It is working here.

It will only change the case as you enter data. It won't change existing text.

Obviously, macros must be enabled.

 

[VoG]

Further to exchange of PMs, change the code to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
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

Save the workbook, exit Excel then try again.

 

[Steve 1962]

Thanks Peter.

Seems to be OK now.

Again, thanks for your help.

Have a good week.

Steve

 

[hugobosstfs]

I am a bit new here but here it goes anyway.

I ahve a sheet like the guy above but when I right click the sheet tab open view source and paste your code there nothing happens. What do i need to do to execute the script to make everything capital letters. Its exactly the same scenario were my colum A has the data I need to have in uppercase. Please help here

 

[VoG]

I am a bit new here but here it goes anyway.

I ahve a sheet like the guy above but when I right click the sheet tab open view source and paste your code there nothing happens. What do i need to do to execute the script to make everything capital letters. Its exactly the same scenario were my colum A has the data I need to have in uppercase. Please help here

You need to ensure that macros are enabled. How you do that depends on your version of Excel.