Code That will Automatically convert a cell's text to Capital...

[Mhz]

Hi, does anyone have a code that will convert a cell's text to Capitals?

The range that will be converted wouold be L10:L30.

by the way, this will be on multiple worksheets...

Thanks for any help on this...

[VBA Noob]

Hi,

This might help

Sub UpperCase()
Dim Rng As Range
Dim Wsht As Worksheet
Set Wsht = ActiveSheet
For i = 1 To Worksheets.Count
Application.ScreenUpdating = False
Sheets(i).Select
For Each Rng In Range("L10:L30")
    If Rng.HasFormula = False Then
        Rng.Value = UCase(Rng.Value)
    End If
Next Rng
Next i
Wsht.Select
Set Wsht = Nothing
Application.ScreenUpdating = True
End Sub

VBA Noob

[Mhz]

Hi VBAnoob, Sorry Just geeting back..

The Formula Works Great! I had to modify where I put it so it will update all
the sheets when the program is opened.. Is there a way to update as you
type in the cell it immediately changes to capital?

If not, The way I use it now will work ok.. Thanks for the Help

[VBA Noob]

Hi,

This link should help

http://www.ozgrid.com/VBA/force-case-text.htm

VBA Noob

[mudraker]

For a single sheet

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   Target.Value = UCase(Target.Value)
   Application.EnableEvents = True
End Sub

For all sheets

'These instructions pre typed & are worded to cater for the novice programmer

'To install macro to correct location

'Copy this macro
'GoTo Excel
'Open VB Editor by pressing Alt + F11
'Just below the menus & toolbars on the lsft you should see Project - VbaProject window
'In this window you will see ThisWorkbook
'double click on ThisWorkbook - opens ThisWorkbook module sheet
'Paste macro code into big window on right

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Application.EnableEvents = False
   Target.Value = UCase(Target.Value)
   Application.EnableEvents = True
End Sub

[Mhz]

Thanks Mudraker Very Much!

Especially for the Novice as myself ... I'm still stuck in BASIC world..

The formula Works Flawlessly... Thanks For The Input.. It's Much Appreciated.

Thanks for the Link VBAnoob...

[mudraker]

thanks for the feedback