Merge two Worksheet_Change codes

[ipbr21054]

Hi,
I habe these two codes.

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("J1:M3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)    With Target
        If .Column = 10 Then Exit Sub
        If .Column = 11 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub

When i try & merge them together i come up with the code below.

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("J1:M3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True


        With Target
        If .Column = 10 Then Exit Sub
        If .Column = 11 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub

Now when i type on the sheet in question i dont see any error messages BUT i also dont get the small case changed to uppercase.

Basically,
The only text on this sheet that needs to be uppercase is as follows.
J1:M3
J4:M4
L5:M35
Other cells are either date ex 13/08/2019 or cost values ex £24.99

Or if you advise something that will just cover the whole range below thats fine.
J1:M38

 

[123DavidRhodes]

This should work but I'm not 100% this is what you're looking for:

Private Sub Worksheet_Change(ByVal Target As Range)

''''''''''''''''''''''''''''''''''''''''''''

'Forces text to UPPER case for the range A1:B20

''''''''''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



On Error Resume Next

If Not Intersect(Target, Range("J1:M38")) Is Nothing Then

Application.EnableEvents = False

Target = UCase(Target)

Application.EnableEvents = True

End If

On Error GoTo 0



End Sub

 

[ipbr21054]

Hi,
Where did the range A1:B20 come from ?

 

[123DavidRhodes]

No where that is a comment not code. I modified the code to the range of cells you specified. I simply did not change the comment is all, sorry.

 

[123DavidRhodes]

No where that is a comment not code. I modified the code to the range of cells you specified. I simply did not change the comment is all, sorry. You're free to change it if you like. It will have no effect however

 

[ipbr21054]

Ok,
I will check.

On a side not when you put code here you need to put it in between tags.
Look on the toolbar where you type your message and you will see this logo #

Click on it and you will see where you type your message the following,


It is here you put your code in between.
So your code will then look like this.

[B]Private Sub Worksheet_Change(ByVal Target As Range)

''''''''''''''''''''''''''''''''''''''''''''

'Forces text to UPPER case for the range A1:B20

''''''''''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub



On Error Resume Next

If Not Intersect(Target, Range("J1:M38")) Is Nothing Then

Application.EnableEvents = False

Target = UCase(Target)

Application.EnableEvents = True

End If

On Error GoTo 0



[B]End Sub[/B][/B]

 

[123DavidRhodes]

I never knew that. I am new here. This is my first day. I will do that in the future

 

[ipbr21054]

No problem i started like that.
Thanks the code works & welcome to the forum