UPPERCASE ENTRY

[craigwojo]

Is there a way to automatically have a UPPERCASE entry (even if the keyboard is set to lowercase) in a cell?

Thank you and God bless,
Craig

 

[Joe4]

Yes, you can use VBA to do that.

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor box that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
End Sub

Then, as long as you have VBA/Macros enabled on your workbook, whenever you make an entry in that worksheet, it will automatically make it all upper case.

If you want to restrict this to certain range(s) on your sheet, we can do that, if you provide those details.

 

[craigwojo]

Thank you Joe4,
What about a column in a spreadsheet? Let's say all the cells in column B after B4.

Thank you.

 

[Joe4]

Thank you Joe4,
What about a column in a spreadsheet? Let's say all the cells in column B after B4.

Thank you.

You can do that like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in cell B4 or below
    If Target.Column = 2 And Target.Row >= 4 Then
        Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If
    
End Sub

 

[craigwojo]

You can do that like this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
  
'   Check to see if entry is made in cell B4 or below
    If Target.Column = 4 And Target.Row >= 2 Then
        Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If
  
End Sub

Hi Joe,
Everything works great. (Remember I'm a newbie with VBA).
I want to add another area to make CAPS within a cell.
I copied the VBA and added it to the bottom of yours and changed "If Target.Column = 2 And Target.Row >= 4 Then" and got an error.
How do I add another area to the VBA without getting an error? Maybe later on I might need to add another area for CAPS and I don't want to bother you with any little change or addon to this spreadsheet.

Your assistance is more than just appreciated. Thank you and God bless,
Craig

 

[MARK858]

Just use an Or statement something like the below

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in cell B4 or below
    If Target.Column = 2 And Target.Row >= 4 Or Target.Column = 5 And Target.Row >= 6 Then
        Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If
    
End Sub