Proper case

[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

 

[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 **

 

[Frank Kabel]

Hi
you may use
application.worksheetfunction.Proper(...)

 

[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.

 

[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

 

[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)

 

[Saju]

Paul & Frank,

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

Regards

Saju