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

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
277
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:

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
277
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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, " ")
Hi mikerickson, thank you I will try this, but how do I incorporate it into my current VBA?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
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

Active Member
Joined
May 31, 2014
Messages
277
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
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

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
May 31, 2014
Messages
277
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,876
Messages
5,627,421
Members
416,245
Latest member
Xterminat

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
Top