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]

You can use the code below - place it in a worksheet code module:

Option Explicit

Private Const rngT = "B:B,D:D,G:G"

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim rng As Range
    Set rng = ActiveSheet.Range(rngT)
    Dim Prng As Range, cc As Range
    Set Prng = Intersect(Target, rng)
    If Not Prng Is Nothing Then
        For Each cc In Prng
            cc.Value = WorksheetFunction.Proper(cc.Value)
        Next cc
    End If
    Set rng = Nothing
    Set cc = Nothing
    Set Prng = Nothing
   
End Sub

change the value of rngT to match the ranges that you need.

Hope this helps.

 

[azad092]

You can use the code below - place it in a worksheet code module:

Option Explicit

Private Const rngT = "B:B,D:D,G:G"

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim rng As Range
    Set rng = ActiveSheet.Range(rngT)
    Dim Prng As Range, cc As Range
    Set Prng = Intersect(Target, rng)
    If Not Prng Is Nothing Then
        For Each cc In Prng
            cc.Value = WorksheetFunction.Proper(cc.Value)
        Next cc
    End If
    Set rng = Nothing
    Set cc = Nothing
    Set Prng = Nothing
  
End Sub

change the value of rngT to match the ranges that you need.

Hope this helps.

thanks for reply... but where i put this code...in this workbook or required sheet ...?

 

[azad092]

thanks for reply... but where i put this code...in this workbook or required sheet ...?

it try ... but not working...

 

[azad092]

it try ... but not working...

Ohhhh no... it works but the excel program crashed at once after typing something in the required cell.

 

[bobsan42]

The code goes in a worksheet module (as I wrote in my post).
Have you modified the code? If so please post the changed one.
Is there any error message or Excel just shuts down?
It would be helpful if you can debug the code and see when it crashes.

 

[dmt32]

Hi,
try following & see if does what you want

Place in your worksheets code page

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo myerror
    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

myerror:
   Application.EnableEvents = True
End Sub

Adjust target columns shown in BOLD as required

Dave

 

[azad092]

Hi,
try following & see if does what you want

Place in your worksheets code page

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo myerror
    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

myerror:
   Application.EnableEvents = True
End Sub

Adjust target columns shown in BOLD as required

Dave

hi
thanks for reply
i apply again your code and i found that i have already applied some coding for selecting multiple items from the dropdown list and due this coding the proper function coding does not work
if i apply this coding in an other worksheet, the code work smothly... so what i do please guide me
thanks

 

[dmt32]

Hi,
my solution was only intended to only meet your original request

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.

and as you state, it does do this.

If you are trying to do something else , then share the coding you have then maybe a solution can be offered by the forum

Dave

 

[azad092]

Hi,
my solution was only intended to only meet your original request



and as you state, it does do this.

If you are trying to do something else , then share the coding you have then maybe a solution can be offered by the forum

Dave

hi
yes dear why not i share the coding...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
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