dannycol
 

I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated

dannycol
 

http://www.mvps.org/dmcritchie/excel/proper.htm#upper


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Dannycol" wrote in message
...
I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated

dannycol
 

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 16:06:02 -0700, Dannycol
wrote:

I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated

Gord Dibben MS Excel MVP

dannycol
 

What i'm trying to do is this: I have blank cells in range L8:L1659 all i
need is the macro to format these cells with capital letters as the data is
entered, which basically eliminates the need to apply the caps key before
entering the data! any help. Thanks



"Gord Dibben" wrote:

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 16:06:02 -0700, Dannycol
wrote:

I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated

Gord Dibben MS Excel MVP

dannycol
 

OK

As the data is entered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const Myrange As String = "L8:L1659"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(Myrange)) Is Nothing Then
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code and goes into a sheet module.

Right-click on your sheet tab and "View Code"

Copy/paste into that module.


Gord

On Thu, 7 Sep 2006 13:24:02 -0700, Dannycol
wrote:

What i'm trying to do is this: I have blank cells in range L8:L1659 all i
need is the macro to format these cells with capital letters as the data is
entered, which basically eliminates the need to apply the caps key before
entering the data! any help. Thanks



"Gord Dibben" wrote:

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 16:06:02 -0700, Dannycol
wrote:

I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated

Gord Dibben MS Excel MVP

dannycol
 

thanks.. it works fine on a blank sheet although,

The sheet i need to insert the macro already has 2 other macros and when i
paste in the additional one i get the following error message: ambiguous name
detected:Worksheet_Change. i checked the help menu and understand there
is a clash with one of the other macros but i dont have a clue how the fix
it. any help?

Regards

"Gord Dibben" wrote:

OK

As the data is entered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const Myrange As String = "L8:L1659"
On Error GoTo ErrHandler
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(Myrange)) Is Nothing Then
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code and goes into a sheet module.

Right-click on your sheet tab and "View Code"

Copy/paste into that module.


Gord

On Thu, 7 Sep 2006 13:24:02 -0700, Dannycol
wrote:

What i'm trying to do is this: I have blank cells in range L8:L1659 all i
need is the macro to format these cells with capital letters as the data is
entered, which basically eliminates the need to apply the caps key before
entering the data! any help. Thanks



"Gord Dibben" wrote:

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 6 Sep 2006 16:06:02 -0700, Dannycol
wrote:

I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated

Gord Dibben MS Excel MVP