Change to Upper Case when a keystroke is made

[garypea123]

Hi,

Is there an automated VBA that will change the text in D4:D12, D22:D35 into upper case letters when a keystroke is made

Someone completes this file, and my OCD ways always want this in UPPER CASE.

Unless these is a simple way (without formulas)

Is

 

[footoo]

I cannot see anywhere on the internet where you can format the cells to turn small case into upper case automatically.

If it was available, then this would be the easiest thing.

Format with a capitals font (e.g. Arial Caps, Felix Titling, etc)
If you don't have any suitable fonts installed, there are plenty of free fonts on the internet available for download.

 

[garypea123]

Yes, its a case I do not have the fonts available.

Not sure how it works with downloading using our business (not even sure if I can download to PC). Also, if I use a font which is not then on someone elses computer, does this not cause any issues.

 

[Joe4]

Yes, its a case I do not have the fonts available.

Not sure how it works with downloading using our business (not even sure if I can download to PC). Also, if I use a font which is not then on someone elses computer, does this not cause any issues.

Then you may have to go with the code suggestions we posted for you.

 

[footoo]

Yes, its a case I do not have the fonts available.

Not sure how it works with downloading using our business (not even sure if I can download to PC). Also, if I use a font which is not then on someone elses computer, does this not cause any issues.

Are you sure you have no capital fonts?
For example, Felix Titling is a standard font that comes with Office (including your version).

 

[garypea123]

Actually when I paste in the text (Felix Titling) it actually works and changes to CAPS.

However, I do not see this still in the drop down search for some reason. So it is available in the back ground, but not actually visible in the fonts area

 

[footoo]

Run this macro in a new blank workbook to see what fonts are installed :

Sub InstalledFonts()
Dim rng As Range, cel As Range
Dim FontList, TempBar, i%
Set FontList = Application.CommandBars("Formatting").FindControl(ID:=1728)
Application.ScreenUpdating = False

'If Font control is missing, create a temp CommandBar
If FontList Is Nothing Then
    Set TempBar = Application.CommandBars.Add
    Set FontList = TempBar.Controls.Add(ID:=1728)
End If

'Put the font names into column A
[A:A].ClearContents
For i = 0 To FontList.ListCount - 1
    Cells(i + 2, 1) = FontList.List(i + 1)
Next i

''Delete temp CommandBar if it exists
On Error Resume Next
TempBar.Delete
On Error GoTo 0

'Put the font styles into column B
Set rng = Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
[B:B].ClearContents
rng = "abcdefghijklmnopqrstuvwxyz  ABCDEFGHIJKLMNOPQRSTUVWXYZ 1234567890"
For Each cel In rng
    cel.Font.Name = cel(1, 0)
Next

''Optional step : Remove Cambria Math fonts
'With [A:A].Find("Cambria Math")(1, 2)
'    .Value = "(This font deliberately omitted - same as Cambria)"
'    .Font.Name = "Arial"
'    .Font.Color = vbRed
'End With

[A:B].EntireColumn.AutoFit
rng.EntireRow.AutoFit

End Sub

 

[aghaffar82]

You can format the data in this font style: Perpetua Titling MT
it will turn everything in small to caps

 

[footoo]

This could be put in the ThisWorkbook module :

Private Sub Workbook_Open()
Sheets("Sheet1").Columns("D").Font.Name = "Felix Titling"
End Sub

 

[garypea123]

Thanks guys, all of this have been very useful, I will definitely use some of the font suggestions and then use the workbook_open function to change everything upon opening. I love to learn these news things