VBA code to enforce Upper & Proper case for different cell range(s)

[ajatshatru]

I'm new to Excel and MrExcel as well so hopefully I have posted this question in the right place.

As I am quite new in VBA stuff, [FONT=Arial, Helvetica Neue, Helvetica, sans-serif]I'm having trouble getting my code to work in VBA.[/FONT]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]My requirement is to enforce UPPER case and Proper case on different cell ranges. I found below thread here:

[/FONT]https://www.mrexcel.com/forum/excel-questions/551180-vba-code-force-uppercase-cells.html

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]I used the code by Peter_SSs for UPPER case and it is working fine. Other than the codes I found elsewhere, this code addressed a common issue of entreating or deleting data from more than one cell at a time.

My issue is, I have to enforce Proper case as well in the same sheet and thus I want to append the code from the above link for Proper case as well, but I do not know how to.

Please help![/FONT]

 

[Truiz]

Using @Peter_SSs code

[COLOR=#00007F][FONT=Courier]Private[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Worksheet_Change([/FONT][/COLOR][COLOR=#00007F][FONT=Courier]ByVal[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Target [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Range)[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Dim[/FONT][/COLOR][COLOR=#333333][FONT=Courier] changed [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Range, c [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] Range[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Dim[/FONT][/COLOR][COLOR=#333333][FONT=Courier] cVal[/FONT][/COLOR]

[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Const[/FONT][/COLOR][COLOR=#333333][FONT=Courier] myR [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]As[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]String[/FONT][/COLOR][COLOR=#333333][FONT=Courier] = "B5:BK16,D3,G20:H22" [/FONT][/COLOR][COLOR=#007F00][FONT=Courier]'<- Your range(s)[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Set[/FONT][/COLOR][COLOR=#333333][FONT=Courier] changed = Intersect(Target, Range(myR))[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]If[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Not[/FONT][/COLOR][COLOR=#333333][FONT=Courier] changed [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Is[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Nothing[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]        Application.EnableEvents = [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]False[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]        [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]For[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Each[/FONT][/COLOR][COLOR=#333333][FONT=Courier] c [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]In[/FONT][/COLOR][COLOR=#333333][FONT=Courier] changed[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]            cVal = c.Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]            [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Select[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Case[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]True[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]                [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Case[/FONT][/COLOR][COLOR=#333333][FONT=Courier] IsEmpty(cVal), IsNumeric(cVal), _[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]                        IsDate(cVal), IsError(cVal)[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]                    [/FONT][/COLOR][COLOR=#007F00][FONT=Courier]' Do nothing[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]                [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Case[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Else[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]                    c.Value = [/FONT][/COLOR][COLOR=#666666][FONT=&quot]WorksheetFunction.Proper[/FONT][/COLOR][COLOR=#333333][FONT=Courier](cVal)[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]            [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Select[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]        [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Next[/FONT][/COLOR][COLOR=#333333][FONT=Courier] c[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]        Application.EnableEvents = [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]True[/FONT][/COLOR]
[COLOR=#333333][FONT=Courier]    [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]If[/FONT][/COLOR]
[COLOR=#00007F][FONT=Courier]End[/FONT][/COLOR][COLOR=#333333][FONT=Courier] [/FONT][/COLOR][COLOR=#00007F][FONT=Courier]Sub[/FONT][/COLOR]

 

[ajatshatru]

Thanks Truiz for the response. As I can understand this code will convert the value is given cell ranges (in the variable myR) to Proper case. But my requirement is to convert a set of cell ranges to Upper case and a set of different cell ranges to Proper. I do not know how to append Peter_SSs so that it can do the both on different cell ranges. I am quite new with VBA thing and not able to do achieve both with the code

 

[ajatshatru]

I managed to do it by editing the code. Can any one tell if this is alright or there is some better way to do this with the help of same code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range, c As Range
    Dim cVal

    Const myUR As String = "K17:AO46,H4:H13, J4:J13,Y4:Y13,AC4:AC13, H17:H46,J17:J46" ',D3,G20:H22" '<- Your range(s)
    
    Const myPR As String = "B4:E13,N4:S13,B16:E45"
    
    
    Set changed1 = Intersect(Target, Range(myPR))
    Set changed2 = Intersect(Target, Range(myUR))
    If Not changed1 Is Nothing Then
        Application.EnableEvents = False
        For Each c In changed1
            cVal = c.Value
            Select Case True
                Case IsEmpty(cVal), IsNumeric(cVal), _
                        IsDate(cVal), IsError(cVal)
                    ' Do nothing
                Case Else
                    'c.Value = UCase(cVal)
                    c.Value = WorksheetFunction.Proper(cVal)
            End Select
        Next c
        Application.EnableEvents = True
    End If
        
        If Not changed2 Is Nothing Then
        Application.EnableEvents = False
        For Each c In changed2
            cVal = c.Value
            Select Case True
                Case IsEmpty(cVal), IsNumeric(cVal), _
                        IsDate(cVal), IsError(cVal)
                    ' Do nothing
                Case Else
                    c.Value = UCase(cVal)
                    'c.Value = WorksheetFunction.Proper(cVal)
            End Select
        Next c
        Application.EnableEvents = True
    End If
End Sub