change Event isn't working

[adeel1]

Hi All

for single cell copy paste code works but when i paste range its isn't working

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo myerror
Application.EnableEvents = False
If Not Intersect(Target, Columns(1)) Is Nothing Then

'If Target <> "" Then
p = Replace(Replace(Evaluate("Proper(""" & Target & """)"), " ", ""), "-", "")

Select Case p

  Case "A"

    Target = "ABC"

  Case "Aa"

    Target = "XXD"


Case Else
    Target = ""
End Select
End If
'End If
myerror:
Application.EnableEvents = True
End Sub

[HansV]

Referring to the value of Target is not valid if Target has multiple cells. Try this version. I added declarations for the variables and made the indentation consistent.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim p As String
    On Error GoTo myerror
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        For Each rng In Intersect(Target, Columns(1))
            p = Replace(Replace(Evaluate("Proper(""" & rng.Value & """)"), " ", ""), "-", "")
            Select Case p
                Case "A"
                    rng.Value = "ABC"
                Case "Aa"
                    rng.Value = "XXD"
                Case Else
                    rng.ClearContents
            End Select
        Next rng
    End If
myerror:
    Application.EnableEvents = True
End Sub

[adeel1]

thx, worked

I think this line isn't required further...

If Not Intersect(Target, Columns(1)) Is Nothing Then

Adeel

[HansV]

In this situation it isn't strictly necessary. If you delete it, don't forget to delete the corresponding End If too.

[adeel1]

thx i deleted it accordingly

Big thx for Netherland for today’s crucial and upset win against South Africa in ICC T20 Cricket World Cup Which led Pakistan into Semifinal…

[HansV]

Good for you. I'm afraid I don't follow cricket...

[adeel1]

Good for you. I'm afraid I don't follow cricket...

i just faced an issue that when i am deleting data by selecting COL A its run the loop for all cells of COL A..

Adeel

[adeel1]

its solved i added below line


If Application.CountA([a2:a1048576]) = 0 Then Exit Sub

i think deleting Data by Selecting Col A is also not good idea i should select range instead

Adeel

[adeel1]

Hi Sir

i am facing an issue that i have couple of vba in standard module referring sheet2 (data will copy from sheet1 to sheet2)and i paste above code in sheet2 module(change event)
now when i am testing code with F8 in standard Module after each line code come in sheet2 module
why this is happening.

i want standard Module codes runs then sheet2 module code run(after data copy in sheet2)

Note, standard Module code also trigging from sheet1 Module with change event( you can ignore this while as i am doing F8 directly from standard Module code)

Capture.JPG

Adeel

[HansV]

When you execute a macro, the Worksheet_Change event procedure of Sheet2 will be called each time the macro changes a cell on sheet2.
While single-stepping through the macro, you can press Shift+F8 instead of F8 to avoid stepping into the Worksheet_Change event procedure (in fact, any other code called directly or indirectly from your macro).

[adeel1]

thx for info, i will share original file here if I did not solve from my end...

Adeel

[DocAElstein]

Hello,
Here is a pretty alternative.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)  '   https://eileenslounge.com/viewtopic.php?f=30&t=38895      https://excelfox.com/forum/showthread.php/2834-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=19028&viewfull=1#post19028
On Error GoTo Myerror
 Let Application.EnableEvents = False
    If Not Intersect(Target, Columns(1)) Is Nothing Then
     Let Target.Value2 = Evaluate("=IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""A"",""ABC"",IF(SUBSTITUTE(SUBSTITUTE(PROPER(" & Target.Address & "),"" "",""""),""-"","""")=""Aa"",""XXD"",""""))")
    Else
    ' case not pasted in column 1
    End If
Myerror:
 Let Application.EnableEvents = True
End Sub

Full story here:
https://excelfox.com/forum/showthread.p ... #post19028
https://excelfox.com/forum/showthread.p ... #post19028
( I am having some problems with URL links breaking over at excelfox after some time has elapsed , so if neither of those work at some time in the future, let me know and I will try again )


Alan

[adeel1]

Much thx for your valuable time and input
Adeel