+ Reply to Thread
Results 1 to 2 of 2

Prevent sub from running if next active cell is in the same row

Hybrid View

  1. #1
    Registered User
    Join Date
    02-18-2004
    MS-Off Ver
    2013 and 365
    Posts
    20

    Prevent sub from running if next active cell is in the same row

    As the title says I've googled and come up with the below but get an Object Require 424 error

    Sub test()
    
    If ActiveCell.Row = CurRow.Row Then Exit Sub
    
    If Not Intersect(ActiveCell, Range("A2:K50")) Is Nothing Then
        
         
        With ActiveSheet.Shapes.Range(Array("tree_ccp"))
    
             .Top = ActiveCell(0, 12).Top
           
        End With
        
       End If
    
    
    End Sub
    
    Sub Worksheet_Change(ByVal Target As Range)
    Dim CurRow As Range
    Set CurRow = ActiveCell
    
    End Sub
    I expect its something simple but I can't puzzle it out

    John

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,681

    Re: Prevent sub from running if next active cell is in the same row

    you declared
    Dim CurRow As Range
    in Worksheet_Change procedure.
    So it's visible only inside this procedure
    not outside (for instance not in test).

    You could have in worksheet code:

    Sub Worksheet_Change(ByVal Target As Range)
    Set CurRow = ActiveCell
    End Sub
    and in standard module:

    Dim CurRow As Range
    
    Sub test()
    
    if CurRow is nothing then
      msgbox "nothing have been changed yet"
    else
      If ActiveCell.Row = CurRow.Row Then Exit Sub
      If Not Intersect(ActiveCell, Range("A2:K50")) Is Nothing Then
        With ActiveSheet.Shapes.Range(Array("tree_ccp"))
            .Top = ActiveCell(0, 12).Top
        End With
     End If
    end if
    End Sub
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to prevent user from running macro until value is entered into specific cell
    By Elliott_the_noob in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2018, 03:04 PM
  2. Validate active cell and prevent mail from firing.
    By kmakjop in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2015, 12:28 PM
  3. [SOLVED] How to prevent worksheet code from running while Module Macro is running:
    By Chad Bateman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2015, 09:26 AM
  4. [SOLVED] Keeping cell active while user form running (not having to select cell twice)
    By clo2peter in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-26-2014, 06:40 AM
  5. Running a different Active.Cell Offset.....
    By Kirtman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 05:52 AM
  6. running vba on active cell selection
    By reggie1000 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2008, 02:07 PM
  7. How to show the active cell after running a macro?
    By shellshock in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-01-2005, 07:18 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1