You could either use a font that is only a caps font or alternatively use the formula:
=UPPER([insert text or cell ref here])
Right click on the sheet name tab and select View Code, in here paste the code below, which is set to only work on A1:A100. If you need all cells, remove the entire first line (If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub). If you need another area watched then change A1:A100 to the range needed.VB:Private Sub Worksheet_Change(ByVal Target As Excel.Range) [I]If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub[/I] Application.EnableEvents = False Target = UCase(Target) Application.EnableEvents = True End Sub
I have since gone back to this spreadsheet and tried this code again and it does not seem to work any more. I have repasted the code but still it is not working. If I open a new work book then it works fine. Is there some setting that I may have set inadvertantly which will stop this code from working?
I used Daves code which works great as you enter text cell by cell but what I want to do is copy a range of cells into A1:A100 and the macro to run converting everything to uppercase.
Currently when I try it I get Error 13 - Type mismatch thown back at me. Can anyone offer some code to handle this?
Its also possible to use Data Validation to force the future entry of text into this range as Capitals or spacea
Data | Validation
It may be that you have interrupted the program and that EnableEvents is currently switched off (it doesn't automatically reset when the program ends). Try the following steps:
1) Go to the Visual Basic Editor (Alt + F11)
2) Open the Immediate Pane (Ctrl + G)
3) Click into the Immediate Pane and enter:
Application.EnableEvents = True (Enter)
4) Return to the worksheet and re-test.
You need to process each cell separately. Try replacing your code with the following:
Hope this helps.VB:Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rngCell As Range If Intersect(Target, Range("A1:D100")) Is Nothing Then Exit Sub Application.EnableEvents = False For Each rngCell In Target.Cells rngCell = UCase(rngCell) Next Application.EnableEvents = True End Sub
There are currently 4 users browsing this thread. (0 members and 4 guests)