How to use the Proper function in Excel for Specific Columns

[azad092]

Hi
i want want that when i type different words in a cell of a specific columns, the first letter of each words should be automatically capital. i know that it could be possible by the use of Proper function (which enable the first letter capital of each word automatically) but how the proper function can be applied through VBA in excel. please guide me if anybody knows
anybody can help me how i can do this

 

[bobsan42]

just put them both into the same procedure:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub

If Not Intersect(Target, Range("B:B,D:D,G:G")) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = WorksheetFunction.Proper(Target.Value)
End If

Application.EnableEvents = True
If Target.Column = 63 Or Target.Column = 70 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[azad092]

just put them both into the same procedure:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub

If Not Intersect(Target, Range("B:B,D:D,G:G")) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = WorksheetFunction.Proper(Target.Value)
End If

Application.EnableEvents = True
If Target.Column = 63 Or Target.Column = 70 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

great work thanks.....