G
Guest
I would like a macro which will format a selected range of cells to have
capital letters.. any help would be appreciated
capital letters.. any help would be appreciated
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
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)
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)
P
Peo Sjoblom
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)
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)
G
Gord Dibben
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
Gord Dibben MS Excel MVP
'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
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
G
Gord Dibben
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
Gord Dibben MS Excel MVP
'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
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
G
Guest
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
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
G
Guest
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
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
G
Gord Dibben
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
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
G
Gord Dibben
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
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
G
Guest
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
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
G
Guest
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
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.