formatting suggestions

M

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
G

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
 
R

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
 
R

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
 
G

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
 
R

RB Smissaert

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

RBS
 
Top