Hi
Is there any way to format a cell in Excel so that any text entered will be shown in uppercase? The cell will be continually updated.
Hi
Is there any way to format a cell in Excel so that any text entered will be shown in uppercase? The cell will be continually updated.
Re: Force a cell to show entered text in Upper case
You could either use a font that is only a caps font or alternatively use the formula:
=UPPER([insert text or cell ref here])
Re: Force a cell to show entered text in Upper case
Hi albelina
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.
Re: Force a cell to show entered text in Upper case
Hi
Thanks for your responses. I used Dave's code and it worked great.
thanks a lot guys
Albelina
Re: Force a cell to show entered text in Upper case
Hi
Me again!
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?
thanks
Re: Force a cell to show entered text in Upper case
Hi all
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?
TIA,
Steve
Re: Force a cell to show entered text in Upper case
Its also possible to use Data Validation to force the future entry of text into this range as Capitals or spacea
Select A1:A1000
Data | Validation
Allow..... Custom
Formula.......=SUMPRODUCT(--((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=32)),--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))=LEN(A1)
Cheers
Dave
Re: Force a cell to show entered text in Upper case
albelina,
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.
Steve,
You need to process each cell separately. Try replacing your code with the following:
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
Display More
Hope this helps.
Regards,
Batman.
Re: Force a cell to show entered text in Upper case
Thanks Batman - this seems to be working fine.
Re: Force a cell to show entered text in Upper case
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
Display More
Hope this helps.
Regards,
Batman.[/QUOTE]
Batman,
I want to use this code to do something slightly different. Can you help me please?
I want to force a cell within the range I set to default to Tahoma size 8 font in black text. How can I add these bits to the code above please?
Thanks and have a great day.
Mark
Re: Force a cell to show entered text in Upper case
Mark@1,
Welcome to the forum
Can you please start your own thread and reference this one if it is relevant to your question.
Don’t have an account yet? Register yourself now and be a part of our community!