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.

 

[Detectiveclem]

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

Hi mikerickson, thank you I will try this, but how do I incorporate it into my current VBA?

 

[KevCarter]

Did the beautiful work fadee2 provided select the correct cells? If so, I made the change to his code to capitalize just the first word rather than all.

Sub Worksheet_Change(ByVal Target As Range)

Dim Colr As Long, Txt As String

If Target.Count = 1 Then

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

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

End If

Application.EnableEvents = False


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

Target.Font.ColorIndex = 16

'Target.Value = Application.WorksheetFunction.Proper(Target.Value)     ' <<<<ammended
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

Else

If Not Intersect(Target, Range("D1,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
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))


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

'Target.Value = Application.WorksheetFunction.Proper(Target.Value)    '<<<<ammended
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

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

Application.EnableEvents = True

End If

End Sub

I'm far from a VBA expert, just trying to provide you with direction. If fadee2 or Mike Erickson have other ideas, I would pay attention to them.

 

[Detectiveclem]

Did the beautiful work fadee2 provided select the correct cells? If so, I made the change to his code to capitalize just the first word rather than all.

Sub Worksheet_Change(ByVal Target As Range)

Dim Colr As Long, Txt As String

If Target.Count = 1 Then

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

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

End If

Application.EnableEvents = False


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

Target.Font.ColorIndex = 16

'Target.Value = Application.WorksheetFunction.Proper(Target.Value)     ' <<<<ammended
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

Else

If Not Intersect(Target, Range("D1,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
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))


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

'Target.Value = Application.WorksheetFunction.Proper(Target.Value)    '<<<<ammended
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

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

Application.EnableEvents = True

End If

End Sub

I'm far from a VBA expert, just trying to provide you with direction. If fadee2 or Mike Erickson have other ideas, I would pay attention to them.

Thank you, so much KevCarter, I will try this first thing tomorrow, as I have meetings to attend for the rest of the day. I will let you know how I get on tomorrow. Thanks again.

 

[KevCarter]

Thank you, so much KevCarter, I will try this first thing tomorrow, as I have meetings to attend for the rest of the day. I will let you know how I get on tomorrow. Thanks again.

Cheers, good luck with it Detectiveclem!

 

[Detectiveclem]

Did the beautiful work fadee2 provided select the correct cells? If so, I made the change to his code to capitalize just the first word rather than all.

Sub Worksheet_Change(ByVal Target As Range)

Dim Colr As Long, Txt As String

If Target.Count = 1 Then

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

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

End If

Application.EnableEvents = False


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

Target.Font.ColorIndex = 16

'Target.Value = Application.WorksheetFunction.Proper(Target.Value)     ' <<<<ammended
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

Else

If Not Intersect(Target, Range("D1,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
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))


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

'Target.Value = Application.WorksheetFunction.Proper(Target.Value)    '<<<<ammended
Target.Value = UCase(Left(Target.Value, 1)) & LCase(Mid(Target.Value, 2))

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

Application.EnableEvents = True

End If

End Sub

I'm far from a VBA expert, just trying to provide you with direction. If fadee2 or Mike Erickson have other ideas, I would pay attention to them.

Hi KevCarter, I have tried your code, but it doesn't work. If you look at my original post, you'll see the code I'm using (successfully), but this includes password protection to unlock and lock my worksheet. Is it not possible (although I don't know how to do it) to add code to this which will
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”

Whilst not affecting the other parts of the code?

Any help to resolve this would be greatly appreciated.

Thank you

 

[mikerickson]

Hi mikerickson, thank you I will try this, but how do I incorporate it into my current VBA?

In your code, when you identify that oneCell is going to be changed, use that logic.

 

[Detectiveclem]

In your code, when you identify that oneCell is going to be changed, use that logic.

Hi Mikerickson, I have no idea what you mean or how to do what you suggest.