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

W

Wandering Mage

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

Doug Glancy

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
 
L

lemmiwinks

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.
Click to expand...

Here are a couple examples of the workbook_sheetdeactivate event in
the ThisWorkbook code pane:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim oldSheetName As String
oldSheetName = Sh.Name
Sh.Range("A1") = "deactivated " & Format(Now, "mm/dd/yy hh:mm:ss")
End Sub
 
N

Nick Hodge

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
[email protected]
 
W

Wandering Mage

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