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

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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

> 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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

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

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

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

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

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

BIG thankyou!

Works well,

Regards,
Simon