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

[Detectiveclem]

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.

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.

 

[fadee2]

try

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

 

[Detectiveclem]

try

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.

 

[fadee2]

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.

 

[KevCarter]

Untested, please try replacing all instances of

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

With

Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

 

[Detectiveclem]

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

 

[Detectiveclem]

Untested, please try replacing all instances of

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

With

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.

 

[KevCarter]

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.

 

[Detectiveclem]

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.

 

[mikerickson]

For multi-sentence cells, I'm thinking something like

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, " ")