+ Reply to Thread
Results 1 to 11 of 11

Prevent code in "Sheet Activate" from running when sheet made visible from other macr

  1. #1
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    Prevent code in "Sheet Activate" from running when sheet made visible from other macr

    Is it possible to prevent code in the Sheet Activate event from running when another macro makes the sheet visible to perform another task?

    I have code that copies the sheet when certain criteria is met on that sheet when it is activated or made visible, however on occassion i need to make that sheet visible for another task via another macro but when it is made visible ifthe criteria is met it makes a copy....which at this point is an undesired effect, is there any way to prevent this from happening?

    regards,
    Simon

  2. #2
    Norman Jones
    Guest

    Re: Prevent code in "Sheet Activate" from running when sheet made visible from other macr

    Hi Simon,

    Try something like:

    Application.EnableEvents = False

    'Your code

    Application.EnableEvents = True

    Also, however, it is rarely necessary to activate or select a worksheet in
    order to manipulate it. It is usually more efficient to assign the sheet to
    a worksheet object variable and operate on the variable, e.g:

    Dim SH As Worksheet

    Set SH = ActiveWorkbook.Sheets("Sheet1")

    SH.Range("A1:A10").Interior.ColorIndex = 6


    ---
    Regards,
    Norman



    "Simon Lloyd" <Simon.Lloyd.29p0hq_1150793718.2531@excelforum-nospam.com>
    wrote in message
    news:Simon.Lloyd.29p0hq_1150793718.2531@excelforum-nospam.com...
    >
    > Is it possible to prevent code in the Sheet Activate event from running
    > when another macro makes the sheet visible to perform another task?
    >
    > I have code that copies the sheet when certain criteria is met on that
    > sheet when it is activated or made visible, however on occassion i need
    > to make that sheet visible for another task via another macro but when
    > it is made visible ifthe criteria is met it makes a copy....which at
    > this point is an undesired effect, is there any way to prevent this
    > from happening?
    >
    > regards,
    > Simon
    >
    >
    > --
    > Simon Lloyd
    > ------------------------------------------------------------------------
    > Simon Lloyd's Profile:
    > http://www.excelforum.com/member.php...fo&userid=6708
    > View this thread: http://www.excelforum.com/showthread...hreadid=553529
    >




  3. #3
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi Norman thanks for both replies...they are related!, below is some code i am using to make sheets visible so that a String search can be made and then select the sheet and cell that has the match, so in my input box i may type "Fred" in it will find all Fred's on the sheets regardless of any other string in the cell and ask if thats the one i want.

    If you know of a smarter way i would appreciate it, as when i make the sheets visible the code that is triggered by certain sheet criteria makes copies of the visible sheets which like i mentioned is undesirable at this point!

    Regards,
    Simon

    Sub FindStuff1()
    Dim colWks As Collection
    Dim wks As Worksheet
    Dim rng As Range
    Dim strFirst As String

    Worksheets("Week1").Visible = True
    Worksheets("Week2").Visible = True
    Worksheets("Week3").Visible = True
    Worksheets("Week4").Visible = True
    Worksheets("Week5").Visible = True
    Worksheets("Week6").Visible = True
    Worksheets("Summary Sheet").Visible = False
    Worksheets("Data Sheet").Visible = False

    Set colWks = New Collection
    colWks.Add Sheets("Week1"), Sheets("Week1").Name
    colWks.Add Sheets("Week2"), Sheets("Week2").Name
    colWks.Add Sheets("Week3"), Sheets("Week3").Name
    colWks.Add Sheets("Week4"), Sheets("Week4").Name
    colWks.Add Sheets("Week5"), Sheets("Week5").Name
    colWks.Add Sheets("Week6"), Sheets("Week6").Name
    Application.ScreenUpdating = True
    t1 = InputBox("Enter Customer Name", "Who To look for?", "")
    Worksheets("Week Selection").Visible = False
    For Each wks In colWks
    Set rng = wks.Cells.Find(What:=t1, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    wks.Select
    rng.Select
    If MsgBox("How about this one... " & rng.Text, _
    vbYesNo, "Customer Found") = vbYes Then _
    Exit Sub
    Set rng = wks.Cells.FindNext(rng)
    Loop Until rng.Address = strFirst
    End If
    Next wks
    MsgBox "Sorry, that was all of them", vbInformation, "Search Complete"
    Worksheets("Week Selection").Visible = True
    Worksheets("Week1").Visible = False
    Worksheets("Week2").Visible = False
    Worksheets("Week3").Visible = False
    Worksheets("Week4").Visible = False
    Worksheets("Week5").Visible = False
    Worksheets("Week6").Visible = False
    Worksheets("Week2").Visible = False
    Worksheets("Summary Sheet").Visible = False
    Worksheets("Data Sheet").Visible = False
    End Sub

  4. #4
    Peter T
    Guest

    Re: Prevent code in "Sheet Activate" from running when sheet made visible from other macr

    > If you know of a smarter way i would appreciate it,

    I know a man that does!

    If the objective is merely what you describe below, ie you don't need
    results for on-going purposes in your code, try Jan Karel Pieterse's
    FlexFind on this page

    http://www.jkp-ads.com/Download.asp

    Regards,
    Peter T


    "Simon Lloyd" <Simon.Lloyd.29p3i2_1150797609.281@excelforum-nospam.com>
    wrote in message
    news:Simon.Lloyd.29p3i2_1150797609.281@excelforum-nospam.com...
    >
    > Hi Norman thanks for both replies...they are related!, below is some
    > code i am using to make sheets visible so that a String search can be
    > made and then select the sheet and cell that has the match, so in my
    > input box i may type "Fred" in it will find all Fred's on the sheets
    > regardless of any other string in the cell and ask if thats the one i
    > want.
    >
    > If you know of a smarter way i would appreciate it, as when i make the
    > sheets visible the code that is triggered by certain sheet criteria
    > makes copies of the visible sheets which like i mentioned is
    > undesirable at this point!
    >
    > Regards,
    > Simon
    >
    > Sub FindStuff1()
    > Dim colWks As Collection
    > Dim wks As Worksheet
    > Dim rng As Range
    > Dim strFirst As String
    >
    > Worksheets("Week1").Visible = True
    > Worksheets("Week2").Visible = True
    > Worksheets("Week3").Visible = True
    > Worksheets("Week4").Visible = True
    > Worksheets("Week5").Visible = True
    > Worksheets("Week6").Visible = True
    > Worksheets("Summary Sheet").Visible = False
    > Worksheets("Data Sheet").Visible = False
    >
    > Set colWks = New Collection
    > colWks.Add Sheets("Week1"), Sheets("Week1").Name
    > colWks.Add Sheets("Week2"), Sheets("Week2").Name
    > colWks.Add Sheets("Week3"), Sheets("Week3").Name
    > colWks.Add Sheets("Week4"), Sheets("Week4").Name
    > colWks.Add Sheets("Week5"), Sheets("Week5").Name
    > colWks.Add Sheets("Week6"), Sheets("Week6").Name
    > Application.ScreenUpdating = True
    > t1 = InputBox("Enter Customer Name", "Who To look for?", "")
    > Worksheets("Week Selection").Visible = False
    > For Each wks In colWks
    > Set rng = wks.Cells.Find(What:=t1, _
    > LookIn:=xlFormulas, _
    > LookAt:=xlPart, _
    > MatchCase:=False)
    > If Not rng Is Nothing Then
    > strFirst = rng.Address
    > Do
    > wks.Select
    > rng.Select
    > If MsgBox("How about this one... " & rng.Text, _
    > vbYesNo, "Customer Found") = vbYes Then _
    > Exit Sub
    > Set rng = wks.Cells.FindNext(rng)
    > Loop Until rng.Address = strFirst
    > End If
    > Next wks
    > MsgBox "Sorry, that was all of them", vbInformation, "Search Complete"
    > Worksheets("Week Selection").Visible = True
    > Worksheets("Week1").Visible = False
    > Worksheets("Week2").Visible = False
    > Worksheets("Week3").Visible = False
    > Worksheets("Week4").Visible = False
    > Worksheets("Week5").Visible = False
    > Worksheets("Week6").Visible = False
    > Worksheets("Week2").Visible = False
    > Worksheets("Summary Sheet").Visible = False
    > Worksheets("Data Sheet").Visible = False
    > End Sub
    >
    >
    > --
    > Simon Lloyd
    > ------------------------------------------------------------------------
    > Simon Lloyd's Profile:

    http://www.excelforum.com/member.php...fo&userid=6708
    > View this thread: http://www.excelforum.com/showthread...hreadid=553529
    >




  5. #5
    Norman Jones
    Guest

    Re: Prevent code in "Sheet Activate" from running when sheet made visible from other macr

    Hi Simon,

    I would heartily concur with Peter's recommendation of Jan Karel Pieterse's
    FlexFind utility.

    However, try the following revision of your code:

    '=============>>
    Public Sub FindStuff1()
    Dim wks As Worksheet
    Dim rng As Range
    Dim strFirst As String
    Dim arr As Variant
    Dim t1 As Variant

    arr = Worksheets(Array("Week1", "Week2", "Week3", "Week4", _
    "Week5", "Week6", "Summary Sheet", "DataSheet"))

    On Error GoTo XIT
    Application.EnableEvents = False

    t1 = InputBox("Enter Customer Name", "Who To look for?", "")

    For Each wks In Worksheets(arr)
    wks.Visible = xksheetvisible
    Set rng = wks.Cells.Find(What:=t1, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    wks.Visible = xlSheetVisible
    wks.Select
    rng.Select
    If MsgBox("How about this one... " & rng.Text, _
    vbYesNo, "Customer Found") = vbYes Then _
    Sheets(arr).Visible = xlSheetHidden
    Exit Sub
    Set rng = wks.Cells.FindNext(rng)
    Loop Until rng.Address = strFirst
    End If
    wks.Visible = xlSheetHidden
    Next wks
    MsgBox PrompT:="Sorry, that was all of them", _
    Buttons:=vbInformation, _
    Title:="Search Complete"

    XIT:
    Application.EnableEvents = True

    End Sub
    '<<=============


    ---
    Regards,
    Norman



    "Simon Lloyd" <Simon.Lloyd.29p3i2_1150797609.281@excelforum-nospam.com>
    wrote in message
    news:Simon.Lloyd.29p3i2_1150797609.281@excelforum-nospam.com...
    >
    > Hi Norman thanks for both replies...they are related!, below is some
    > code i am using to make sheets visible so that a String search can be
    > made and then select the sheet and cell that has the match, so in my
    > input box i may type "Fred" in it will find all Fred's on the sheets
    > regardless of any other string in the cell and ask if thats the one i
    > want.
    >
    > If you know of a smarter way i would appreciate it, as when i make the
    > sheets visible the code that is triggered by certain sheet criteria
    > makes copies of the visible sheets which like i mentioned is
    > undesirable at this point!
    >
    > Regards,
    > Simon
    >
    > Sub FindStuff1()
    > Dim colWks As Collection
    > Dim wks As Worksheet
    > Dim rng As Range
    > Dim strFirst As String
    >
    > Worksheets("Week1").Visible = True
    > Worksheets("Week2").Visible = True
    > Worksheets("Week3").Visible = True
    > Worksheets("Week4").Visible = True
    > Worksheets("Week5").Visible = True
    > Worksheets("Week6").Visible = True
    > Worksheets("Summary Sheet").Visible = False
    > Worksheets("Data Sheet").Visible = False
    >
    > Set colWks = New Collection
    > colWks.Add Sheets("Week1"), Sheets("Week1").Name
    > colWks.Add Sheets("Week2"), Sheets("Week2").Name
    > colWks.Add Sheets("Week3"), Sheets("Week3").Name
    > colWks.Add Sheets("Week4"), Sheets("Week4").Name
    > colWks.Add Sheets("Week5"), Sheets("Week5").Name
    > colWks.Add Sheets("Week6"), Sheets("Week6").Name
    > Application.ScreenUpdating = True
    > t1 = InputBox("Enter Customer Name", "Who To look for?", "")
    > Worksheets("Week Selection").Visible = False
    > For Each wks In colWks
    > Set rng = wks.Cells.Find(What:=t1, _
    > LookIn:=xlFormulas, _
    > LookAt:=xlPart, _
    > MatchCase:=False)
    > If Not rng Is Nothing Then
    > strFirst = rng.Address
    > Do
    > wks.Select
    > rng.Select
    > If MsgBox("How about this one... " & rng.Text, _
    > vbYesNo, "Customer Found") = vbYes Then _
    > Exit Sub
    > Set rng = wks.Cells.FindNext(rng)
    > Loop Until rng.Address = strFirst
    > End If
    > Next wks
    > MsgBox "Sorry, that was all of them", vbInformation, "Search Complete"
    > Worksheets("Week Selection").Visible = True
    > Worksheets("Week1").Visible = False
    > Worksheets("Week2").Visible = False
    > Worksheets("Week3").Visible = False
    > Worksheets("Week4").Visible = False
    > Worksheets("Week5").Visible = False
    > Worksheets("Week6").Visible = False
    > Worksheets("Week2").Visible = False
    > Worksheets("Summary Sheet").Visible = False
    > Worksheets("Data Sheet").Visible = False
    > End Sub
    >
    >
    > --
    > Simon Lloyd
    > ------------------------------------------------------------------------
    > Simon Lloyd's Profile:
    > http://www.excelforum.com/member.php...fo&userid=6708
    > View this thread: http://www.excelforum.com/showthread...hreadid=553529
    >




  6. #6
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Thanks both Peter and Norman, i don't have a download facility here so will try that recommendation later!, Norman....I can't believe my code looked so messy and lengthy......or at least I didn't until I saw your revision

    Thanks again for the replies!

    Regards,
    Simon

  7. #7
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi Norman, i made a couple of typo changes to your revised code, there is only one problem with it......when it finds the first value of t1 regardless of whether you click yes or no the code stops on the first sheet it found the match on......any ideas why?......the code is initiated from a front sheet called "Week Selection".

    regards,
    Simon

    Public Sub FindStuff1()
    Dim wks As Worksheet
    Dim rng As Range
    Dim strFirst As String
    Dim arr As Variant
    Dim t1 As Variant

    arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", "Week6", "Summary Sheet", "Data Sheet")

    On Error GoTo XIT
    Application.EnableEvents = False

    t1 = InputBox("Enter Customer Name", "Who To look for?", "")

    For Each wks In Worksheets(arr)
    wks.Visible = xlSheetVisible
    Set rng = wks.Cells.Find(What:=t1, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    wks.Visible = xlSheetVisible
    wks.Select
    rng.Select
    If MsgBox("How about this one... " & rng.Text, _
    vbYesNo, "Customer Found") = vbYes Then _
    Sheets(arr).Visible = xlSheetHidden
    Exit Sub
    Set rng = wks.Cells.FindNext(rng)
    Loop Until rng.Address = strFirst
    End If
    wks.Visible = xlSheetHidden
    Next wks
    MsgBox PrompT:="Sorry, that was all of them", _
    Buttons:=vbInformation, _
    Title:="Search Complete"

    XIT:
    Application.EnableEvents = True
    End Sub

  8. #8
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi Norman, i made a couple of typo changes to your revised code, there is only one problem with it......when it finds the first value of t1 regardless of whether you click yes or no the code stops on the first sheet it found the match on......any ideas why?......the code is initiated from a front sheet called "Week Selection".

    regards,
    Simon

    Public Sub FindStuff1()
    Dim wks As Worksheet
    Dim rng As Range
    Dim strFirst As String
    Dim arr As Variant
    Dim t1 As Variant

    arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", "Week6", "Summary Sheet", "Data Sheet")

    On Error GoTo XIT
    Application.EnableEvents = False

    t1 = InputBox("Enter Customer Name", "Who To look for?", "")

    For Each wks In Worksheets(arr)
    wks.Visible = xlSheetVisible
    Set rng = wks.Cells.Find(What:=t1, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    wks.Visible = xlSheetVisible
    wks.Select
    rng.Select
    If MsgBox("How about this one... " & rng.Text, _
    vbYesNo, "Customer Found") = vbYes Then _
    Sheets(arr).Visible = xlSheetHidden
    Exit Sub
    Set rng = wks.Cells.FindNext(rng)
    Loop Until rng.Address = strFirst
    End If
    wks.Visible = xlSheetHidden
    Next wks
    MsgBox PrompT:="Sorry, that was all of them", _
    Buttons:=vbInformation, _
    Title:="Search Complete"

    XIT:
    Application.EnableEvents = True
    End Sub

  9. #9
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi Norman, i made a couple of typo changes to your revised code, there is only one problem with it......when it finds the first value of t1 regardless of whether you click yes or no the code stops on the first sheet it found the match on......any ideas why?......the code is initiated from a front sheet called "Week Selection".

    regards,
    Simon

    Public Sub FindStuff1()
    Dim wks As Worksheet
    Dim rng As Range
    Dim strFirst As String
    Dim arr As Variant
    Dim t1 As Variant

    arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", "Week6", "Summary Sheet", "Data Sheet")

    On Error GoTo XIT
    Application.EnableEvents = False

    t1 = InputBox("Enter Customer Name", "Who To look for?", "")

    For Each wks In Worksheets(arr)
    wks.Visible = xlSheetVisible
    Set rng = wks.Cells.Find(What:=t1, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    wks.Visible = xlSheetVisible
    wks.Select
    rng.Select
    If MsgBox("How about this one... " & rng.Text, _
    vbYesNo, "Customer Found") = vbYes Then _
    Sheets(arr).Visible = xlSheetHidden
    Exit Sub
    Set rng = wks.Cells.FindNext(rng)
    Loop Until rng.Address = strFirst
    End If
    wks.Visible = xlSheetHidden
    Next wks
    MsgBox PrompT:="Sorry, that was all of them", _
    Buttons:=vbInformation, _
    Title:="Search Complete"

    XIT:
    Application.EnableEvents = True
    End Sub

  10. #10
    Norman Jones
    Guest

    Re: Prevent code in "Sheet Activate" from running when sheet made visible from other macr

    Hi Simon,

    Try:

    '=============>>
    Public Sub FindStuff2()
    Dim wks As Worksheet
    Dim rng As Range
    Dim strFirst As String
    Dim arr As Variant
    Dim t1 As Variant

    arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _
    "Week6", "Summary Sheet", "DataSheet")

    On Error GoTo XIT
    Application.EnableEvents = False

    t1 = InputBox("Enter Customer Name", "Who To look for?", "")

    For Each wks In Worksheets(arr)
    wks.Visible = xlSheetVisible
    Set rng = wks.Cells.Find(What:=t1, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not rng Is Nothing Then
    strFirst = rng.Address
    Do
    wks.Select
    rng.Select
    If MsgBox("How about this one... " & rng.Text, _
    vbYesNo, "Customer Found") = vbYes Then
    Exit Sub
    End If
    Set rng = wks.Cells.FindNext(rng)
    Loop Until rng.Address = strFirst
    End If
    wks.Visible = xlSheetHidden
    Next wks
    MsgBox PrompT:="Sorry, that was all of them", _
    Buttons:=vbInformation, _
    Title:="Search Complete"

    XIT:
    Application.EnableEvents = True

    End Sub
    '<<=============


    ---
    Regards,
    Norman



    "Simon Lloyd" <Simon.Lloyd.29pjh9_1150818405.983@excelforum-nospam.com>
    wrote in message
    news:Simon.Lloyd.29pjh9_1150818405.983@excelforum-nospam.com...
    >
    > Hi Norman, i made a couple of typo changes to your revised code, there
    > is only one problem with it......when it finds the first value of t1
    > regardless of whether you click yes or no the code stops on the first
    > sheet it found the match on......any ideas why?......the code is
    > initiated from a front sheet called "Week Selection".
    >
    > regards,
    > Simon
    >
    > Public Sub FindStuff1()
    > Dim wks As Worksheet
    > Dim rng As Range
    > Dim strFirst As String
    > Dim arr As Variant
    > Dim t1 As Variant
    >
    > arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", "Week6",
    > "Summary Sheet", "Data Sheet")
    >
    > On Error GoTo XIT
    > Application.EnableEvents = False
    >
    > t1 = InputBox("Enter Customer Name", "Who To look for?", "")
    >
    > For Each wks In Worksheets(arr)
    > wks.Visible = xlSheetVisible
    > Set rng = wks.Cells.Find(What:=t1, _
    > LookIn:=xlFormulas, _
    > LookAt:=xlPart, _
    > MatchCase:=False)
    > If Not rng Is Nothing Then
    > strFirst = rng.Address
    > Do
    > wks.Visible = xlSheetVisible
    > wks.Select
    > rng.Select
    > If MsgBox("How about this one... " & rng.Text, _
    > vbYesNo, "Customer Found") = vbYes Then _
    > Sheets(arr).Visible = xlSheetHidden
    > Exit Sub
    > Set rng = wks.Cells.FindNext(rng)
    > Loop Until rng.Address = strFirst
    > End If
    > wks.Visible = xlSheetHidden
    > Next wks
    > MsgBox PrompT:="Sorry, that was all of them", _
    > Buttons:=vbInformation, _
    > Title:="Search Complete"
    >
    > XIT:
    > Application.EnableEvents = True
    > End Sub
    >
    >
    > --
    > Simon Lloyd
    > ------------------------------------------------------------------------
    > Simon Lloyd's Profile:
    > http://www.excelforum.com/member.php...fo&userid=6708
    > View this thread: http://www.excelforum.com/showthread...hreadid=553529
    >




  11. #11
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    BIG thankyou!

    Works well,

    Regards,
    Simon

+ 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