Excel capitalisation question...

  xphile 07:50 15 Sep 2007

Is it possible to format an excel workbook or indeed a single sheet to automatically convert all entered text into capital letters. I.e. when I enter Craig or craig in a cell, excel will convert it to CRAIG & do this for all cells containing letters ?

  Forum Editor 08:00 15 Sep 2007

(He'll be along soon), but I imagine you'll need to use the VBA 'change event' procedure for this. While you're waiting for someone who really knows what they're talking about you could give this a try (change the A1:A10 reference for the range you want to apply).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
'Target.Value = StrConv(Target.Text, vbLowerCase)
Target.Value = StrConv(Target.Text, vbUpperCase)
'Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
Application.EnableEvents = True
End Sub

  VoG II 08:15 15 Sep 2007

... I see your Excel skills are improving FE. Actually a rather simpler version will do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub
Target.Value = UCase(Target.Value)
End Sub

To use this, right click the sheet tab and select View Code then copy and paste the above code into the code window. Close the code window and enter a value in the range A1 to B10 (you can amend this range in the code as you wish). Text will be capitalised; other data such as numbers will be ignored by default.

  xphile 08:23 15 Sep 2007

That's perfect. Thank You FE & VoG. If I want to do this on other sheets I add, do I have to repeat the above for every sheet, or, can I assign the above to the workbook to default to this for every sheet I add ?

  Forum Editor 08:33 15 Sep 2007

Thank you, but I think we both know who the real expert is.

  VoG II 08:36 15 Sep 2007

First delete the code that you entered in the worksheet module (right click the sheet tab, View Code, select everything in the code window and press delete, then close the code window).

Right click the Excel logo just to the left of File on the menu bar. Select View Code. Copy and paste in

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:B10")) Is Nothing Then Exit Sub
Target.Value = UCase(Target.Value)
End Sub

then close the code window.

If you are using Excel 2007 you need a different method to access the workbook's code window. Press ALT + F11 to open the Visual Basic Editor. In the VBA Project window, top left, if necessary click the + next to VBAProject to expand the list of objects, right click ThisWorkbook and select View Code then proceed as before.

  Taff™ 08:48 15 Sep 2007

VoG™ showed us a link to ASAP Utilities several years ago click here and I must admit it`s used fairly frequently by me for this sort of thing. A free for personal use program and it will do this (and many other things) without having to know all the code. Simply select the cells, rows or columns and use the ASAP menu which is installed within Excel as an add on.

  xphile 08:56 15 Sep 2007

VoG - Thank You that is perfect & just what I wanted. Didn't even know that excel icon was there !

Taff - Thanks for the link, will check it out.

  Taff™ 09:02 15 Sep 2007

You won`t regret it, will they VoG™ ?

  VoG II 09:52 15 Sep 2007

Yes - ASAP Utilities is excellent. However, it won't do what the code does which is to capitalise data as it is entered.

  Taff™ 09:56 15 Sep 2007

Thanks VoG™. Hadn`t appreciated that. Most of the spreadsheets I use are either self generated or come to me for database cleansing and sending mailshots to a Society I administer. I find ASAP a doddle to correct addresses that members have filled in on-line using all caps!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

Black Friday Deals for Designers & Artists: Adobe, Apple, Corel Painter, Microsoft Surface, Wacom &e…

Best Black Friday Apple Deals 2017

Black Friday 2017 : date, sites participants & bonnes affaires