Merge two Worksheet_Change codes

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I habe these two codes.

Code:
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

Code:
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.

Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.

Code:
[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]
 
Upvote 0
No problem i started like that.
Thanks the code works & welcome to the forum
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top