Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Prevent code in "Sheet Activate" from running when sheet made visible from other macr
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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



Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why does "cannot shift objects off sheet" prevent row insertion? Idaho old guy Excel Discussion (Misc queries) 21 August 19th 08 10:36 AM
Excel "Move or Copy" and "Delete" sheet functions dsiama Excel Worksheet Functions 1 December 28th 07 01:57 PM
Password visible if user selects "view code" kcdonaldson Excel Worksheet Functions 2 December 3rd 05 03:48 PM
Please add a "sheet" function like "row" and "column" functions Spreadsheet Monkey Excel Programming 2 November 8th 05 04:08 PM
disabling the "view code" tab - sheet protection? neowok[_43_] Excel Programming 1 March 5th 04 02:48 PM


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement