Results 1 to 3 of 3

Thread: Worksheet_Change event

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0

    Worksheet_Change event

    I want to show error message & delete the content entered in A2; when Range A1 is blank & User enter value in A2
    i try below code but its not working properly. it went in loop.
    and is there any way where i can hide multiple rows in single line statement
    like Union(Rows("3:4"),Rows("9:10").....
    i will appreciate any help on this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" And Range("A1").Value = "" Then
    Temp = MsgBox("Error: ", vbInformation, "Warning")
    Range("A2").ClearContents
    Range("A1").Select
    End If
    End Sub

  2. #2
    Member Charles's Avatar
    Join Date
    Aug 2012
    Posts
    63
    Rep Power
    13
    Hi,

    You need to set the "Application.EnableEvents" to False
    prior to the change and after the change to True.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" And Range("A1").Text = "" Then
    
    Temp = MsgBox("Error: ", vbInformation, "Warning")
    Application.EnableEvents = False
    Range("A2").ClearContents
    Range("A1").Select
    Application.EnableEvents = True
    End If
    End Sub


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
    https://www.youtube.com/watch?v=jdPeMPT98QU
    https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-20-2023 at 03:06 PM.

  3. #3
    Junior Member
    Join Date
    Apr 2012
    Posts
    21
    Rep Power
    0
    thanks a lot Charles..! it works perfect...!

Similar Threads

  1. Worksheet Change Event
    By jamilm in forum Excel Help
    Replies: 2
    Last Post: 12-29-2012, 12:06 AM
  2. Excel freezing when using .ontimer event
    By Rasm in forum Excel Help
    Replies: 5
    Last Post: 11-26-2012, 12:48 PM
  3. Left and Right Mouse Click Event
    By PcMax in forum Excel Help
    Replies: 10
    Last Post: 11-24-2012, 04:54 AM
  4. Event target range
    By bobkap in forum Excel Help
    Replies: 3
    Last Post: 09-13-2012, 05:34 AM
  5. help with after insert event in Access 10
    By richlyn in forum Access Help
    Replies: 9
    Last Post: 03-03-2012, 10:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •