Results 1 to 8 of 8

Thread: Merge two Worksheet_Change codes

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Merge two Worksheet_Change codes

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

    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

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

    Hi,
    Where did the range A1:B20 come from ?
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

    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.

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

    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

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

    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:
    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

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

  8. #8
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge two Worksheet_Change codes

    No problem i started like that.
    Thanks the code works & welcome to the forum
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •