Is there a way to call selected sheet's name from on deactivate event?
 

I am using a Workbook Level Sheet Deactivate Event. I
need to know the name of the sheet that I am trying to
select. Is there anyway to call out this name? Thanks.

Is there a way to call selected sheet's name from on deactivate event?
 

Since the Deactivate event fires before the Activate event, it seems like
you might have to do it the other way around, i.e., trap the deactivated
sheet name in the Activate event:

Option Explicit
Dim deactivated As Worksheet

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set deactivated = Sh.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox deactivated.Name
End Sub

hth,

Doug Glancy

"Wandering Mage" wrote in message
...
I am using a Workbook Level Sheet Deactivate Event. I
need to know the name of the sheet that I am trying to
select. Is there anyway to call out this name? Thanks.

Is there a way to call selected sheet's name from on deactivate event?
 

Can we try and understand this better?

If you want to know the sheet you are going to select next then you will not
get that from a de-activate event as it is over at de-activation and before
activation of the next one.

You would only be able to get the sheet name from the sh. object in the
Sheet_Activate event.

Perhaps if you explain what you are trying to do, someone may be able to
give a better pointer

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Wandering Mage" wrote in message
...
I am using a Workbook Level Sheet Deactivate Event. I
need to know the name of the sheet that I am trying to
select. Is there anyway to call out this name? Thanks.

Is there a way to call selected sheet's name from on deactivate event?
 

Certainly, and thank you for the response. That is
exactly what I am getting at, I can't find a way to call
out the target sheet from sheet deactivate sheet event, as
you said. Sounds like I might not be able to. Basically,
I want to write an if statement that will Exit Sub if the
target sheet's first 2 letters ar "LC". Problem is I
can't check the name, cause I don't know if I can call it
out. It's kind of complicated, I am writing a very large
sheet that is very controlled. I created temporary sheets
that are "LC1_", "LC2_" and so on, and suffixed by
something that is predetermined elswhere in the workbook.
There is other parts of the on Deactivate so far that will
raise a prompt box saying that it will delete these
temporary sheets (the "LC*_" ones), and ask you if you are
sure you want to continue. If you say yes, it will delete
all the psuedo temporary "LC*_" sheets. If you say no, it
will return you to the original sheet. Only problem is, I
want to be able to select other "LC_*" sheets without the
prompt box comming up. Right now I can only view one of
the LC's because yes will delete them, and no will return
me to the starting sheet. I already have an If statement
that will only call out the deactivate macro if the
deactivated sheet starts with "LC". Basically, it would
be easier for me to write the macro with an If statement
that exits sub on the deactivate event. But I can't call
out the target sheet to get its name and determine if you
are trying to go to another "LC" sheet or something else
:(

Thanks!
-----Original Message-----
Can we try and understand this better?

If you want to know the sheet you are going to select
next then you will not
get that from a de-activate event as it is over at de-
activation and before
activation of the next one.

You would only be able to get the sheet name from the sh.
object in the
Sheet_Activate event.

Perhaps if you explain what you are trying to do, someone
may be able to
give a better pointer

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England



"Wandering Mage"
wrote in message
...
I am using a Workbook Level Sheet Deactivate Event. I
need to know the name of the sheet that I am trying to
select. Is there anyway to call out this name? Thanks.


.