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

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

"Saju" wrote in message
...
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 & Frank,

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

Regards

Saju

-----Original Message-----
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 **

"Saju" wrote in
message
...
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


.

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

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

--
Jim Rech
Excel MVP

"Frank Kabel" wrote in message
...
| 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

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.

--
Regards,
Tom Ogilvy


"Saju" wrote in message
...
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

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" wrote in message
...
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