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
You do not have the required permissions to view the files attached to this post.
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.
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
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
You do not have the required permissions to view the files attached to this post.
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).
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