I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

Hi,

Attach this to a button

Sub Marine()
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Mike

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

I added the Dim line, but get compile error:
"For Each control variable must be Variant or Object."

Sub ChangeToUpperCase()
Dim c As String
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Former submariner?

"Mike H" wrote:

Hi,

Attach this to a button

Sub Marine()
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Mike

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

Sam,

Using .value is taking an unnecessary risk. What it will do is change any
formula selected (maybe by accident) to values. Use .formula instead or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

"Sam Wilson" wrote:

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

What did you dim it as ? It should be

Dim c as Range

Mike

"Phil H" wrote:

I added the Dim line, but get compile error:
"For Each control variable must be Variant or Object."

Sub ChangeToUpperCase()
Dim c As String
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Former submariner?

"Mike H" wrote:

Hi,

Attach this to a button

Sub Marine()
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Mike

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

True, but imagine

A1 = "BlahBlahBlah"
B1 = "=A1"

If you use ucase(Range("B1").formula) you won't end up with BLAHBLAHBLAH

Sam

"Mike H" wrote:

Sam,

Using .value is taking an unnecessary risk. What it will do is change any
formula selected (maybe by accident) to values. Use .formula instead or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

"Sam Wilson" wrote:

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

One possible problem with your method... if the one or more cells in the
selection have formulas in them, and if those formulas have quoted text in
them, then the quoted text will all be converted to upper case as well. I
think the idea you posted to Sam may be the way to go here...

Sub Marine()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then C.Value = UCase(C.Value)
Next
End Sub

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

Attach this to a button

Sub Marine()
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Mike

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to
upper
case for any range of selected cells. Can someone help?

If Not r.HasFormula Then
r.Value = UCase(r.Value)
else
r.formula = "=UPPER(" & right(r.formula,len(r.formula)-1) & ")"
end if

maybe

"Sam Wilson" wrote:

True, but imagine

A1 = "BlahBlahBlah"
B1 = "=A1"

If you use ucase(Range("B1").formula) you won't end up with BLAHBLAHBLAH

Sam

"Mike H" wrote:

Sam,

Using .value is taking an unnecessary risk. What it will do is change any
formula selected (maybe by accident) to values. Use .formula instead or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

"Sam Wilson" wrote:

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

If the formula returns a lower case text string then this won't work. If you
try to insert =Upper(<old formula) in the macro you convert numbers to
text...

=IF(ISNUMBER(<old formula),VALUE(<old formula),UPPER(<old formula))

could work though, but it's an ugly solution.

I think the only solution is to use a font like Felix Titling.


"Rick Rothstein" wrote:

One possible problem with your method... if the one or more cells in the
selection have formulas in them, and if those formulas have quoted text in
them, then the quoted text will all be converted to upper case as well. I
think the idea you posted to Sam may be the way to go here...

Sub Marine()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then C.Value = UCase(C.Value)
Next
End Sub

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

Attach this to a button

Sub Marine()
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Mike

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to
upper
case for any range of selected cells. Can someone help?

Thands guys for your input - appreciate it. So what is the final macro?

"Sam Wilson" wrote:


If Not r.HasFormula Then
r.Value = UCase(r.Value)
else
r.formula = "=UPPER(" & right(r.formula,len(r.formula)-1) & ")"
end if

maybe

"Sam Wilson" wrote:

True, but imagine

A1 = "BlahBlahBlah"
B1 = "=A1"

If you use ucase(Range("B1").formula) you won't end up with BLAHBLAHBLAH

Sam

"Mike H" wrote:

Sam,

Using .value is taking an unnecessary risk. What it will do is change any
formula selected (maybe by accident) to values. Use .formula instead or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

"Sam Wilson" wrote:

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

Try code like the following:


Sub AAA()
Dim RR As Range
Dim R As Range
On Error GoTo ErrH:
Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each R In RR.Cells
R.Value = UCase(R.Value)
Next R
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Select the range of cells to change to upper case and run the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 10 Sep 2009 07:18:01 -0700, Phil H <Phil
wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

Well I guess this macro would handle all of the concerns raised in this
thread...

Sub UpperCaseRange()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then
C.Value = UCase(C.Value)
ElseIf IsNumeric(C.Text) Then
C.Value = UCase(C.Value)
Else
C.Formula = "=UPPER(" & Mid(C.Formula, 2) & ")"
End If
Next
End Sub

--
Rick (MVP - Excel)


"Phil H" wrote in message
...
Thands guys for your input - appreciate it. So what is the final macro?

"Sam Wilson" wrote:


If Not r.HasFormula Then
r.Value = UCase(r.Value)
else
r.formula = "=UPPER(" & right(r.formula,len(r.formula)-1) & ")"
end if

maybe

"Sam Wilson" wrote:

True, but imagine

A1 = "BlahBlahBlah"
B1 = "=A1"

If you use ucase(Range("B1").formula) you won't end up with
BLAHBLAHBLAH

Sam

"Mike H" wrote:

Sam,

Using .value is taking an unnecessary risk. What it will do is change
any
formula selected (maybe by accident) to values. Use .formula instead
or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

"Sam Wilson" wrote:

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change
text to upper
case for any range of selected cells. Can someone help?

Thanks, Rick. Works as expected.

"Rick Rothstein" wrote:

Well I guess this macro would handle all of the concerns raised in this
thread...

Sub UpperCaseRange()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then
C.Value = UCase(C.Value)
ElseIf IsNumeric(C.Text) Then
C.Value = UCase(C.Value)
Else
C.Formula = "=UPPER(" & Mid(C.Formula, 2) & ")"
End If
Next
End Sub

--
Rick (MVP - Excel)


"Phil H" wrote in message
...
Thands guys for your input - appreciate it. So what is the final macro?

"Sam Wilson" wrote:


If Not r.HasFormula Then
r.Value = UCase(r.Value)
else
r.formula = "=UPPER(" & right(r.formula,len(r.formula)-1) & ")"
end if

maybe

"Sam Wilson" wrote:

True, but imagine

A1 = "BlahBlahBlah"
B1 = "=A1"

If you use ucase(Range("B1").formula) you won't end up with
BLAHBLAHBLAH

Sam

"Mike H" wrote:

Sam,

Using .value is taking an unnecessary risk. What it will do is change
any
formula selected (maybe by accident) to values. Use .formula instead
or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

"Sam Wilson" wrote:

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

"Phil H" wrote:

I need a macro, which will be attached to a button, to change
text to upper
case for any range of selected cells. Can someone help?

Thanks, Chip. I kept this macro as well as Rick's. Your macro changes all
cells in the worksheet to UC. Useful.

"Chip Pearson" wrote:

Try code like the following:


Sub AAA()
Dim RR As Range
Dim R As Range
On Error GoTo ErrH:
Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each R In RR.Cells
R.Value = UCase(R.Value)
Next R
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Select the range of cells to change to upper case and run the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 10 Sep 2009 07:18:01 -0700, Phil H <Phil
wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

Your macro changes all
cells in the worksheet to UC.

Yeah, that is due to a problem (about which I had forgotten) with
SpecialCells when no cells are found and you have a selection of a
single cell. SpecialCells erroneously uses the entire sheet. You can
change

Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)

to

Set RR = Application.Intersect( _
Selection, _
Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues))

to work around the problem. This still has the advantage that it only
looks at cells without formulas and that contain text, as opposed to
numeric, values. It takes the test for HasFormula and the
unnecessary conversion of numeric values out of the code and wraps
them more efficiently up in the SpecialCells test.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 11 Sep 2009 04:24:01 -0700, Phil H
wrote:

Thanks, Chip. I kept this macro as well as Rick's. Your macro changes all
cells in the worksheet to UC. Useful.

"Chip Pearson" wrote:

Try code like the following:


Sub AAA()
Dim RR As Range
Dim R As Range
On Error GoTo ErrH:
Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each R In RR.Cells
R.Value = UCase(R.Value)
Next R
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Select the range of cells to change to upper case and run the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 10 Sep 2009 07:18:01 -0700, Phil H <Phil
wrote:

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?