Proper case

S

Saju

Hi All,
Is there an equivalent in VB for woorksheet function Proper
(). Also, Is there an equvalent of Word's sentence case
functionality in Excel and VB. Thanks for your comments

Regards
Saju
 
P

Paul B

Saju, try this, if you put it in your personal workbook it will be available
to all you workbooks

Sub TextConvert()
'By Ivan F Moala
'will change the text that you have selected,
'if no text is selected it will change the whole sheet
Dim ocell As Range
Dim Ans As String

Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If Ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **
 
T

Tom Ogilvy

StrConv(string, conversion)

for conversion use

vbProperCase


sStr = StrConv(sStr, vbProperCase)

I have no Idea waht Word's sentence case functionality is, but try using
strconv with your sentence.
 
J

Jim Rech

Using the StrConv keeps you in VB. Not that there's anything wrong with
jumping over to Excel.

--
Jim Rech
Excel MVP

| Hi
| you may use
| application.worksheetfunction.Proper(...)
|
| --
| Regards
| Frank Kabel
| Frankfurt, Germany
|
|
| Saju wrote:
| > Hi All,
| > Is there an equivalent in VB for woorksheet function Proper
| > (). Also, Is there an equvalent of Word's sentence case
| > functionality in Excel and VB. Thanks for your comments
| >
| > Regards
| > Saju
 
B

Bob Phillips

Hi Saju,

You could use the Proper worksheet function as Frank says, but if you want
an application independent VBA function, this is what I use

'---------------------------------------------------------------------
Public Function Capitalize(Name As String), _
Optional Delim As String = " "
'---------------------------------------------------------------------
Dim aParts
Dim i As Long

aParts = Split(LCase(RemoveMultipleSpaces(Name)), Delim)
For i = LBound(aParts, 1) To UBound(aParts, 1)
aParts(i) = UCase(Left(aParts(i), 1)) & _
Right(aParts(i), Len(aParts(i)) - 1)
Next i
Capitalize = Join(aParts, Delim)

End Function



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Saju

Paul & Frank,

Thank you vary much for your help. This is very useful.

Regards

Saju
 
Top