formatting suggestions

[marina madeleine]

Hi,

I have 2 questions and would appreciate some suggestions on these:

1) How can I convert all the contents of a worksheet from capital
letters to small letters in one step?

2) How can I do a clean option to clear invisible codes on all the
contents of a worksheet. I know how to do it for one cell at a time ie.
clean (cell A). But would be interested in knowing how to do this for a
whole worksheet as well as all the worksheets in a workbook in one step.

Thanks for your ideas.

Marina

 

[GJones]

Hi Marina;

If you use Microsoft Word as an automation object you will
be able to use the "smallcaps" property for text to change
all the letters to not be capitalized.

Go into VBA in word and search the VBA help for smallcaps
or small caps. Below is what I found pasted in.

Thanks,

Greg


SmallCaps Property
See Also Applies To Example Specifics
True if the font is formatted as small capital letters.
Returns True, False or wdUndefined (a mixture of True and
False). Can be set to True, False, or wdToggle. Read/write
Long.

expression.SmallCaps

expression Required. An expression that returns a Font
object.

Remarks
Setting the SmallCaps property to True sets the AllCaps
property to False, and vice versa.

Example
This example demonstrates the difference between small
capital letters and all capital letters in a new document.

Set myRange = Documents.Add.Content
With myRange
.InsertAfter "This is a demonstration of SmallCaps."
.Words(6).Font.SmallCaps = True
.InsertParagraphAfter
.InsertAfter "This is a demonstration of AllCaps."
.Words(14).Font.AllCaps = True
End With
This example formats the entire selection as small capital
letters if part of the selection is already formatted as
small capital letters.

If Selection.Type = wdSelectionNormal Then
mySel = Selection.Font.SmallCaps
If mySel = wdUndefined Then Selection.Font.SmallCaps =
True
Else
MsgBox "You need to select some text."
End If

 

[RB Smissaert]

Quesion 1:

Sub test()

Dim c As Range

For Each c In ActiveSheet.UsedRange
c = UCase(c)
Next

End Sub


Question 2:

Sub test2()

Dim c As Range

For Each c In ActiveSheet.UsedRange
c = WorksheetFunction.Clean(c)
Next

End Sub


RBS

 

[marina madeleine]

Many thanks to all for the suggestions. I will try them out.

Marina

 

[RB Smissaert]

Forgot you wanted it for all the sheets in the workbook:

Sub test2()

Dim sh As Worksheet
Dim c As Range

For Each sh In ActiveWorkbook.Sheets
For Each c In sh.UsedRange
c = WorksheetFunction.Clean(c)
Next
Next

End Sub


RBS

 

[Tom Ogilvy]

smallcaps <> small letters

 

[Gord Dibben]

Marina

Be caultious when using RBS's code to change to UCase.

First, you wanted to go from UPPER case to LOWER case so code would read LCase
not UCase

Second, it will wipe out(change to values) any formulas you have in the
selected range.

Gord Dibben Excel MVP

 

[RB Smissaert]

Sorry; yes, I just gave the general idea and
didn't work it all out further.

RBS

 

[marina madeleine]

It works, thanks to all for the code (RB Smissaert) and to everyone for
the suggestions!

Marina