• 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...

  • 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?

Frncis

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

Frncis

Member
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:
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 !
 

Marc L

Excel Ninja

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 …
 

Hui

Excel Ninja
Staff member
Because the Ranges are the same you can just include it once

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

Frncis

Member
Because the Ranges are the same you can just include it once

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

Frncis

Member
I tried data validation, but I am not clear what I am do in wrong. Here is the code.
Code:
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
 

Frncis

Member
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.
 

Attachments

Last edited by a moderator:

Frncis

Member
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.
 

Attachments

Marc L

Excel Ninja

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

Frncis

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

Frncis

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

Marc L

Excel Ninja

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

What the event must exactly perform ?​
 

Frncis

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

What the event must exactly perform ?​
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:
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
 

Attachments

Last edited:

Marc L

Excel Ninja

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

Frncis

Member
Seeing your actual code the letter is converted to
upper case only in source Sheet1, is it really what you need ?​
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.
 

Marc L

Excel Ninja
As a beginner starter :​
Code:
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 !
 

Frncis

Member
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:
Top