How to Capitalise the First letter in each sentence and add to existing VBA Code.

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
320
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I need to Capitalise the First letter of each sentence within the following cells.

“D47:D49,D69,D73:D75,D78,D82,D85,D87,D89,D92,D94:D97,D110,D113,D116,D119:D122,D124,D127,D129:D130,D123,D135,D145,D151,D159,D162,D164:D165,D169:D172,D174,D176,D181,D183,D187:D188,D190”

I.E. if some enters in D47 “the big Cat in a Cage is dangerous. it neeDs to be Locked in” the text should change to “The big cat in a cage is dangerous. It needs to be locked in” When the person moves to the next cell.

I already been greatly helped with the following Code which works brilliantly, however can you assist me add the code needed to solve the above into the code below for me please.



VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Colr As Long, Txt As String

If Target.Count = 1 Then

If Not Intersect(Target, Range("D8:D195")) Is Nothing Then

Txt = Sheets("CodeTemplate").Range(Target.Address).Value

End If

Application.EnableEvents = False

Me.Unprotect "MyPassword" '<<< UNPROTECT SHEET

If Len(Target.Value) = 0 Or Target.Value = Txt Then

Target.Font.ColorIndex = 16

Target.Value = Txt

Else

If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57,D71,D76,D80,D83,D91,D100,D109,D155,D160,D186,D193")) Is Nothing Then

Target.Value = Application.Proper(Target.Value)



ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then

Target.Value = UCase(Target.Value)

End If 'existing code

Debug.Print Target.Address, Target.Cells(1, 1).Value, Me.ProtectContents 'Added

Target.Font.Color = RGB(0, 0, 0) 'Added

' Target.Font.ColorIndex = 1 'Commented, ie not working

End If 'existing code

Me.Protect " MyPassword " 'PROTECT SHEET

Application.EnableEvents = True

End If

End Sub



Thank you for anyone who can assist me with this.
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
try
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Colr As Long, Txt As String

If Target.Count = 1 Then

If Not Intersect(Target, Range("D8:D195")) Is Nothing Then

Txt = Sheets("CodeTemplate").Range(Target.Address).Value

End If

Application.EnableEvents = False

Me.Unprotect "MyPassword" '<<< UNPROTECT SHEET

If Len(Target.Value) = 0 Or Target.Value = Txt Then

Target.Font.ColorIndex = 16

Target.Value = Application.WorksheetFunction.Proper(Txt)    '<<<<ammended

Else

If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57,D71,D76,D80,D83,D91,D100,D109,D155,D160,D186,D193")) Is Nothing Then

Target.Value = Application.WorksheetFunction.Proper(Target.Value)     ' <<<<ammended



ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then

Target.Value = Application.WorkSheetFunction.Proper(Target.Value)    '<<<<ammended

End If 'existing code

Debug.Print Target.Address, Target.Cells(1, 1).Value, Me.ProtectContents 'Added

Target.Font.Color = RGB(0, 0, 0) 'Added

' Target.Font.ColorIndex = 1 'Commented, ie not working

End If 'existing code

Me.Protect " MyPassword " 'PROTECT SHEET

Application.EnableEvents = True

End If

End Sub
 
Upvote 0
try
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Colr As Long, Txt As String

If Target.Count = 1 Then

If Not Intersect(Target, Range("D8:D195")) Is Nothing Then

Txt = Sheets("CodeTemplate").Range(Target.Address).Value

End If

Application.EnableEvents = False

Me.Unprotect "MyPassword" '<<< UNPROTECT SHEET

If Len(Target.Value) = 0 Or Target.Value = Txt Then

Target.Font.ColorIndex = 16

Target.Value = Application.WorksheetFunction.Proper(Txt)    '<<<<ammended

Else

If Not Intersect(Target, Range("D13:D17,D20,D22,D24:D27,D32:D35,D51:D57,D71,D76,D80,D83,D91,D100,D109,D155,D160,D186,D193")) Is Nothing Then

Target.Value = Application.WorksheetFunction.Proper(Target.Value)     ' <<<<ammended



ElseIf Not Intersect(Target, Range("D30,D38,D60")) Is Nothing Then

Target.Value = Application.WorkSheetFunction.Proper(Target.Value)    '<<<<ammended

End If 'existing code

Debug.Print Target.Address, Target.Cells(1, 1).Value, Me.ProtectContents 'Added

Target.Font.Color = RGB(0, 0, 0) 'Added

' Target.Font.ColorIndex = 1 'Commented, ie not working

End If 'existing code

Me.Protect " MyPassword " 'PROTECT SHEET

Application.EnableEvents = True

End If

End Sub
Hi fadee2,

I replaced the code with your code, but it wouldn't run and the previous functions set up failed to work. Thank you for at least trying to help me.
 
Upvote 0
well I tried to simply replace the function.
i would suggest provision of sample data to go with the code, that way it would be much easier to understand and provide the solution rather then deciphering the query itself.
 
Upvote 0
Untested, please try replacing all instances of

VBA Code:
Target.Value = Application.WorkSheetFunction.Proper(Target.Value)

With

VBA Code:
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))
 
Upvote 0
Hi, I was hoping that by following the code already included in my initial post it would be possible to slot in the additional code to ensure the cells I have identified need to have the first letter capitalised in each sentence (or if this is not possible the very first letter, in the cell automatically changes to capital when you tab out of the cell).
 
Upvote 0
Untested, please try replacing all instances of

VBA Code:
Target.Value = Application.WorkSheetFunction.Proper(Target.Value)

With

VBA Code:
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))
Hi KevCarter,

Will the changes you suggest target all cells? If so this won't work for me. I have plenty of cells where I don't want any changes to occur and other cells have existing code (see my code in the initial post) to change certain cells to UCase, others to Proper Text.
 
Upvote 0
Hi KevCarter,

Will the changes you suggest target all cells? If so this won't work for me. I have plenty of cells where I don't want any changes to occur and other cells have existing code (see my code in the initial post) to change certain cells to UCase, others to Proper Text.
You just make this change to the code you or DetectiveClem already posted to target the cells you wish to change.
 
Upvote 0
You just make this change to the code you or DetectiveClem already posted to target the cells you wish to change.
Thank you KevCarter,

Sorry I am a real novice with VBA and don't know how I would do this. Is it possible that you could insert the additional code you suggest into my original code. Or lead me through the process. Sorry if I am being a little dense here.
 
Upvote 0
For multi-sentence cells, I'm thinking something like
VBA Code:
Dim Words as Variant, Flag as boolean, i As Long

Words = Split(oneCell.Text, " ")

Flag = True

For i = 0 To UBound(Words)
    Words(i) = StrConv(Words(i), IIF(Flag, vbProperCase, vbLowerCase)
    Flag = Words(i) Like "*."
Next i

oneCell.Value = Join(Words, " ")
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top