CODEWITHSUNDEEP
excelvbastring
Wafir Manakad
Wafir Manakad

Reputation: 81

How to capitalize first letter after opening bracket and apply on entire worksheet?

I am using the following VBA code to convert text to Proper case:

Sub Proper()
Dim ws As Object
Dim LCell As Range

'Move through each sheet in your spreadsheet
For Each ws In ActiveWorkbook.Sheets

   'Convert all constants and text values to proper case
    For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)
       LCell.Formula = StrConv(LCell.Formula, vbProperCase)
    Next

Next ws
End Sub

I need to add to the function. After an opening bracket, the first letter should be capitalized.

Here is an example: google (android) should become Google (Android).

Is there a way to edit the above code to add this rule, or do I have to loop each character through an If condition?

Upvotes: 2

Views: 619

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

You can use the Worksheet function instead:

Sub Proper()
Dim ws As Object
Dim LCell As Range

'Move through each sheet in your spreadsheet
For Each ws In ActiveWorkbook.Sheets

 '  Convert all constants and text values to proper case
    For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)
       LCell.Value = Application.WorksheetFunction.Proper(LCell)
    Next

Next ws
End Sub

Upvotes: 3

Related Questions