change Event isn't working

adeel1
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

change Event isn't working

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

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: change Event isn't working

Post by 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
Regards,
Hans

adeel1
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

Post by adeel1 »

thx, worked :clapping: :clapping: :clapping:

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

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

Adeel

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: change Event isn't working

Post by HansV »

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

adeel1
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

Post by adeel1 »

thx i deleted it accordingly :clapping:

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… :grin: :grin:

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: change Event isn't working

Post by HansV »

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

adeel1
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

Post by adeel1 »

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

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
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

Post by 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
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

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

User avatar
HansV
Administrator
Posts: 75751
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: change Event isn't working

Post by 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).
Regards,
Hans

adeel1
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

Post by adeel1 »

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

Adeel

User avatar
DocAElstein
3StarLounger
Posts: 263
Joined: 18 Jan 2022, 15:59
Location: Naked, in Hof, Bavaria, Germany

Re: change Event isn't working

Post by 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
You do not have the required permissions to view the files attached to this post.
Sure you can give me a DVM instead, when you take the AVOmeter out of my cold dead hands,

adeel1
3StarLounger
Posts: 237
Joined: 04 Oct 2017, 15:47

Re: change Event isn't working

Post by adeel1 »

Much thx for your valuable time and input :clapping: :thankyou:
Adeel