1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a way to have 2 Private Sub Worksheet_Change events?

Discussion in 'VBA Macros' started by Frncis, Aug 14, 2018.

  1. Frncis

    Frncis Member

    Messages:
    61
    I have code that Chihiro provided to copy just the letter H to a second sheet.
    The following code, changes all entries to Upper Case.
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not (Application.Intersect(Target, Range("D4:Q29")) _
          Is Nothing) Then
            With Target
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
        End If
    End Sub
     
    It also uses Private Sub Worksheet_Change(ByVal Target As Range), the same as the copy code. When I compile the total code, I get the following error message: Compile error: Ambiguous name detected Worksheet_Change
    The above code works on sheet 2, because there is no other code on sheet 2, that uses Private Sub Worksheet_Change(BYVal target as Range).
    I am trying to force the entries on sheet 1 to be Upper case. I really don't want to use Data Validation. The case change on sheet 2 is not noticeable to the user, & I am trying to do the same to sheet 1.
  2. Frncis

    Frncis Member

    Messages:
    61
    I want to apologize to every one for asking for help & then figuring it out my self. I Learned that you can only have 1 Private Sub Worksheet_Change(ByVal Target As Range), per worksheet, but you can combine multiples. I just played with the code & it was easier than expected. Here is my combined code:
    Code (vb):

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D4:Q29")) Is Nothing Then
        Sheet2.Range(Target.Address).Value = Target.Value
    End If

    If Not (Application.Intersect(Target, Range("D4:Q29")) _
          Is Nothing) Then
            With Target
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
        End If
    End Sub

     

    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

  3. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    Post any VBA question in the VBA section of this forum !

    You forgot to desactivate the events in the first If block !
    See also the VBA inner help of If / ElseIf / End If …
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,667
    Because the Ranges are the same you can just include it once

    Code (vb):

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D4:Q29")) Is Nothing Then
        Sheet2.Range(Target.Address).Value = Target.Value
       
        With Target
             If Not .HasFormula Then
                 Application.EnableEvents = False
                 .Value = UCase(.Value)
                  Application.EnableEvents = True
             End If
        End With
    End If
    End Sub
    Thomas Kuriakose likes this.
  5. Frncis

    Frncis Member

    Messages:
    61
    I found that I still have one issue that I Have not been able to resolve.
    Ever entry on sheet 1 is copied to sheet 2. There is a text entry "H" and numerical ".25 up to 8". I want to copy only the text entry. I have tried <> "H", & that didn't work. Any suggestions.

    Also I want to thank the moderator, for moving the question to the proper forum.
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,258


    If the text part is always a single letter then use the Left function …​
  7. Frncis

    Frncis Member

    Messages:
    61
    Marc L thanks for that piece of information. I now have a better understanding of Left function. How do I prevent numerical entries from copying? (.25 to 8)
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,258


    Maybe via the cell data validation …​
  9. Frncis

    Frncis Member

    Messages:
    61
    Are you taking VBA or standard data validation?
    If you are talking VBA, I don't quite understand, how that is possible.
  10. Frncis

    Frncis Member

    Messages:
    61
    I tried data validation, but I am not clear what I am do in wrong. Here is the code.
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("D4:Q29")) Is Nothing Then
        Sheet2.Range(Target.Address).Value = Target.Value
        With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=H"
    End With
    End If

    If Not (Application.Intersect(Target, Range("D4:Q29")) _
          Is Nothing) Then
            With Target
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
        End If
    End Sub
  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    Standard validation using a formula or the cell chars length,
    avoid under VBA …
    As it depends clearly on what is expected in the cell & as I can't guess !
  12. Frncis

    Frncis Member

    Messages:
    61
    The days for the holidays would float, so I don't think that regular validation would not work. I was trying to find a copy of the file so you could see what I am talking about. At the time I sent the response, I could not locate it. The attached file will not have the previous code that I posted, but you will see what the file looks like.

    Attached Files:

    Last edited by a moderator: Aug 15, 2018
  13. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    I let someone else try to download your workbook
    as my system blocks it … (virus alert)​
  14. Frncis

    Frncis Member

    Messages:
    61
    I wonder if it is the macros on the workbook. I am trying to copy only the letter H.to a second sheet. I did post a stripped down version, with no macros. if a virus warning pops up, I don't know.

    Attached Files:

  15. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    Yes you're right (twice since yesterday)
    but as I can download other macro workbooks …
    I have to go right now.​
  16. Frncis

    Frncis Member

    Messages:
    61
    I guess that this is 3 for 3. I went to a copy of the file, that I had on CD. Apparently the file had been corrupted. Thank god for CDs.
    Last edited by a moderator: Aug 15, 2018
  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    So what is allowed or forbidden in the cell (and which one) ?
  18. Frncis

    Frncis Member

    Messages:
    61
    Marc L in an earlier post, you mention using the Left function since I have only one letter that is being copied. Could you please explain, how that would work, & where I would place in the code below. I have googled & not found any clear guidance about use, or placement.
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D4:Q29")) Is Nothing Then
        Sheet2.Range(Target.Address).Value = Target.Value
    End If

    If Not (Application.Intersect(Target, Range("D4:Q29")) _
          Is Nothing) Then
            With Target
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
        End If
    End Sub
  19. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    Just use the function in the code where you need it …

    What the event must exactly perform ?​
  20. vletm

    vletm Excel Ninja

    Messages:
    4,415
    Frncis
    Just for interesting ...
    How would You take care cases
    ... if/after You 'clear' Your 'copied H' from the 2nd sheet?
  21. Frncis

    Frncis Member

    Messages:
    61
    What I trying to do is copy only the letter H from sheet 1 to sheet 2, & to convert the letter H to upper case. Both sheets are identical in cell range. I have included both a sanitized sample file & the current code that copies & converts to upper case. H = holliday. So each year, the cells that H is located will be different. The code is located as worksheet code.

    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D4:Q29")) Is Nothing Then
        Sheet2.Range(Target.Address).Value = Target.Value
    End If

    If Not (Application.Intersect(Target, Range("D4:Q29")) _
          Is Nothing) Then
            With Target
                If Not .HasFormula Then
                    Application.EnableEvents = False
                    .Value = UCase(.Value)
                    Application.EnableEvents = True
                End If
            End With
        End If
    End Sub

    Attached Files:

    Last edited: Aug 16, 2018
  22. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    Seeing your actual code the letter is converted to
    upper case only in source Sheet1, is it really what you need ?​
  23. Frncis

    Frncis Member

    Messages:
    61
    Yes. Sheet 2 has its own conversion code. Originally it started out with just the conversion. However I was asked to see if it would be possible to have "H" copied, so the user would not have to past it twice, & to same time.
    However I am open to suggestions.
  24. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    As a beginner starter :​
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count = 1 And Not Intersect([D4:Q29], Target) Is Nothing And Not Target.HasFormula Then
               S$ = UCase$(Left$(Target.Value2, 1))
            If S > "" Then
                Application.EnableEvents = False
                If S <> "H" Then Beep: Target.ClearContents Else Target.Value2 = S: Sheet2.Range(Target.Address).Value2 = S
                Application.EnableEvents = True
            End If
        End If
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
  25. Frncis

    Frncis Member

    Messages:
    61
    I ran it on a test file & it gave me the following error on .ClearContents Compile Error: Invalid or unqualified reference.

    I have a question, also. Am I correct in assuming that I don't need that upper case code on sheet 2.

    The help states that: An identifier beginning with a period is valid only within a With block
    It also says that the cause could be the period. A solution is to complete the qualification of the identifier or remove the period. I removed the period & received the following error : Sub, Function, or Property not defined (Error 35). It mentioned calling the procedure that you want. At this point I am lost.
    Last edited by a moderator: Aug 16, 2018

Share This Page