+ Reply to Thread
Results 1 to 2 of 2

Restrict Workbook_SheetSelectionChange to only occur after another macro has finished

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-16-2011
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    405

    Question Restrict Workbook_SheetSelectionChange to only occur after another macro has finished

    Hi guys

    I am using the following code to redefine all the named ranges every time a worksheet changes. Is it possible to restrict this to happen only after another macro has finished its operation - the problem is that i have a macro that flicks between sheets regularly to copy and paste data into order. I need to be able to restrict the sheetSelectionChange to only happen after my other macro has completed

    Thanks heaps

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call AddNamedRanges(ThisWorkbook.Name)
    End Sub
    The code below searches for top and bottom borders for the used range in the worksheet it is called. when it knows the row #'s for the range it extends the selection to Row H then names the range according to the value found in the top left cell of the selected range
    '----------------------------------------------------------------------------------
    Sub AddNamedRanges(WbNm As String)
    
        Dim Cll As Range
        Dim RngStart As Range
        Workbooks(WbNm).Activate
        
        For Each Cll In ActiveSheet.UsedRange.Columns(1).Cells
            If Cll.Borders(xlEdgeTop).LineStyle = xlContinuous Then
                Set RngStart = Cll
            ElseIf Cll.Borders(xlEdgeBottom).LineStyle = xlContinuous And Not RngStart Is Nothing Then
                ActiveWorkbook.Names.Add Name:=Replace(RngStart.Value, " ", ""), RefersTo:=ActiveSheet.Range(RngStart, Cll.Offset(0, 7))
    
                Set RngStart = Nothing
            End If
            
        Next Cll
    
    End Sub
    Last edited by jordan2322; 01-16-2012 at 08:23 PM.

  2. #2
    Forum Contributor
    Join Date
    11-16-2011
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    405

    Re: Restrict Workbook_SheetSelectionChange to only occur after another macro has fini

    Dont worry guys i got around having to do this
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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