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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Prevent code in "Sheet Activate" from running when sheet made visible from other macr (https://www.excelbanter.com/excel-programming/364790-prevent-code-sheet-activate-running-when-sheet-made-visible-other-macr.html)

Simon Lloyd[_794_]

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

--
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

Norman Jones

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"
wrote in message
...

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

Simon Lloyd[_795_]

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

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

Peter T

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"
wrote in message
...

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

Norman Jones

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"
wrote in message
...

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

Simon Lloyd[_796_]

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

Thanks both Peter and Norman, i don't have a download facility here s
will try that recommendation later!, Norman....I can't believe my cod
looked so messy and lengthy......or at least I didn't until I saw you
revision :)

Thanks again for the replies!

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=55352

Simon Lloyd[_797_]

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

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

Simon Lloyd[_798_]

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

Simon Lloyd[_799_]

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

Norman Jones

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"
wrote in message
...

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

Simon Lloyd[_801_]

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

BIG thankyou!

Works well,

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

All times are GMT +1. The time now is 10:02 PM.