Consulting

Results 1 to 8 of 8

Thread: 2 Vba Routines - stop working

  1. #1
    VBAX Regular
    Joined
    May 2018
    Posts
    9
    Location

    2 Vba Routines - stop working

    I have 2 VBA routines. 1 is assigned to a button to clear cells the user has placed values in at the sheet level. The 2nd is to copy cells to other cells based on a drop down menus selection is in a macro.

    I am having the following issues:
    1. After the 1st time the clear cells button is clicked, the cells stop copying (macro1)



    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
      Dim cell As Range
      
    
    
    '   Check to see if cell G3 updated
      If Target.Address = Range("G3").Address Then
           If Target.Value = "YES" Then
             Application.EnableEvents = False
             Range("E9:E12").Value = Range("J3:J6").Value
             Application.EnableEvents = True
      Else
      If Target.Address = Range("G3").Address Then
           If Target.Value = "NO" Then
             Application.EnableEvents = False
             Range("E9:E12").ClearContents
             Application.EnableEvents = True
          End If
         End If
        End If
      End If
            
    End Sub
    Sub Clearselected()
    Range("e3").ClearContents
    Range("e9", "e12").ClearContents
    Range("H9", "H12").ClearContents
    Range("i3", "i6").ClearContents
    Range("K9", "K12").ClearContents
    End Sub
    Last edited by SamT; 05-06-2018 at 01:56 PM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    First Run this
    Sub Reset_Events
    'Keep this sub around. EnableEvents is tricky
    'When in doubt, Run this.
    Application.EnableEvents = True
    End Sub
    Then see if this works better
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check to see if cell G3 updated
    If Target.Address <> "$G$3" Then Exit Sub
    
    Application.EnableEvents = False
    
    If Target.Value = "YES" Then
      Range("E9:E12").Value = Range("J3:J6").Value
    ElseIf Target.Value = "NO" Then
      Range("E9:E12").ClearContents
    End If
    
    Application.EnableEvents = True
    
    End Sub
    Also
    Sub Clearselected()
    Application.EnableEvents = False
        Range("e3").ClearContents
        Range("e9", "e12").ClearContents
        Range("H9", "H12").ClearContents
        Range("i3", "i6").ClearContents
        Range("K9", "K12").ClearContents
    Application.EnableEvents = True
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    May 2018
    Posts
    9
    Location
    I am not sure if I understand:
    1) Run Reset Events 1 time only as a module?

    If that is the case, I did that. However, after doing the clearselected (button/module) the copy (sheet1) of the cells stops working (j3:j6 - e9:e12)

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    I usually run Reset_Events whenever anything stops working. Application.Events is tricky like The Joker. Reset_Events can be in any Code Page. I keep it in MyPersonal.xls so it's always available. You'd be amazed at how often people write bad code that fails after Application.EnableEvents = false but before Application.EnableEvents = True.

    Try using
    If UCase(Target) =
    Is J9:J12 formulas? If not, try
    Range("J9:J12").Copy Range("E9")
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    May 2018
    Posts
    9
    Location
    Quote Originally Posted by SamT View Post
    I usually run Reset_Events whenever anything stops working. Application.Events is tricky like The Joker. Reset_Events can be in any Code Page. I keep it in MyPersonal.xls so it's always available. You'd be amazed at how often people write bad code that fails after Application.EnableEvents = false but before Application.EnableEvents = True.Try using
    If UCase(Target) =
    Is J9:J12 formulas? If not, try
    Range("J9:J12").Copy Range("E9")

  6. #6
    VBAX Regular
    Joined
    May 2018
    Posts
    9
    Location
    I made the code changes. J9:J12 is defined as accounting. The first time I ran it, the bottom grid line of E12 disappeared. I submitted reset_events after that happened. I reran and the results were the same. E9 - E12 did not copy to J9 - J12. Below is my code:

    UPDATE - PLEASE READ BELOW THEN LOOK AT THE RED BELOW THIS

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check to see if cell G3 updated
    If Target.Address <> "$G$3" Then Exit Sub


    Application.EnableEvents = False


    If UCase(Target) = "YES" Then
    Range("J9:J12").Copy Range("E9")
    ElseIf UCase(Target) = "NO" Then
    Range("E9:E12").ClearContents
    End If


    Application.EnableEvents = True


    End Sub

    UPDATE
    The original code you sent to me (below) will work if I toggle between YES and NO after doing the CLEAR

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check to see if cell G3 updated
    If Target.Address <> "$G$3" Then Exit Sub


    Application.EnableEvents = False


    If Target.Value = "YES" Then
    Range("E9:E12").Value = Range("J3:J6").Value
    ElseIf Target.Value = "NO" Then
    Range("E9:E12").ClearContents
    End If


    Application.EnableEvents = True


    End Sub
    Last edited by jgold20; 05-07-2018 at 02:29 AM. Reason: Update to problem

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    The original code you sent to me (below) will work if I toggle between YES and NO after doing the CLEAR
    Well, of course. The Change Event Sub only runs after you change G3.

    Add this line as the first line in the sub
    Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox"Running the Change sub."
    'etc
    '
    Then Clear the cells
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Regular
    Joined
    May 2018
    Posts
    9
    Location
    The code you supplied does work following the below procedure:
    1) Click clear contents.
    2) Make sure drop down = no
    3) Enter info.
    4) Change drop down = yes - fields are updated

    or

    If I do not click clear contents, I am able to toggle back and forth between YES & NO without any problems - this is the part that puzzles me

Posting Permissions

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