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:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
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
 

Detectiveclem

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

fadee2

Active Member
Joined
Nov 7, 2020
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Detectiveclem

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

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

ADVERTISEMENT

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.
 

KevCarter

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

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,976
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, " ")
 

Watch MrExcel Video

Forum statistics

Threads
1,128,117
Messages
5,628,790
Members
416,340
Latest member
PJB1102

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