Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was ActiveSheet before changed to new sheet?
I've been searching for hours, can't find a solution but it's probably
simple...how can I tell what sheet was the ActiveSheet BEFORE they changed to the current activesheet. Example: User is on a particular sheet (let's say they're on "Main"), then they click the tab to go to the "Prices" sheet. How can I tell what sheet they're comming FROM - in this case the "Main" sheet? In the Workbook and the Worksheet events Activate and Deactivate, I check the Sheet parameter (SH) and it always shows "Prices", the sheet they're going too. Thanks for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was ActiveSheet before changed to new sheet?
Working OK here.
2 sheets, in the sheet1 code module: Private Sub Worksheet_Activate() MsgBox "activate", , "sheet1" End Sub Private Sub Worksheet_Deactivate() MsgBox "de-activate", , "sheet1" End Sub in the sheet2 code module: Private Sub Worksheet_Activate() MsgBox "activate", , "sheet2" End Sub Private Sub Worksheet_Deactivate() MsgBox "de-activate", , "sheet2" End Sub First I get the Deactivate from the sheet I came from, then the Activate event from the sheet I went to. Try for yourself. RBS "Tom L" wrote in message ... I've been searching for hours, can't find a solution but it's probably simple...how can I tell what sheet was the ActiveSheet BEFORE they changed to the current activesheet. Example: User is on a particular sheet (let's say they're on "Main"), then they click the tab to go to the "Prices" sheet. How can I tell what sheet they're comming FROM - in this case the "Main" sheet? In the Workbook and the Worksheet events Activate and Deactivate, I check the Sheet parameter (SH) and it always shows "Prices", the sheet they're going too. Thanks for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was ActiveSheet before changed to new sheet?
Tom, this code indicates one approach. Put it in your ThisWorkbook module.
Regards, Bill Private g_strLastSheetName As String Private Sub Workbook_Open() g_strLastSheetName = ThisWorkbook.ActiveSheet.Name End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call MsgBox("last sheet: " & g_strLastSheetName) g_strLastSheetName = ThisWorkbook.ActiveSheet.Name End Sub "Tom L" wrote: I've been searching for hours, can't find a solution but it's probably simple...how can I tell what sheet was the ActiveSheet BEFORE they changed to the current activesheet. Example: User is on a particular sheet (let's say they're on "Main"), then they click the tab to go to the "Prices" sheet. How can I tell what sheet they're comming FROM - in this case the "Main" sheet? In the Workbook and the Worksheet events Activate and Deactivate, I check the Sheet parameter (SH) and it always shows "Prices", the sheet they're going too. Thanks for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was ActiveSheet before changed to new sheet?
You could try this in the thisworkbook module -
Dim mSh As Object Dim mShtName As String Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim sName As String Dim sMsg As String On Error Resume Next sName = mSh.Name On Error GoTo 0 If sName = "" Then sMsg = mShtName & " has just been deleted" Else sMsg = mShtName & " deactivated" End If MsgBox sMsg, , Sh.Name & " activated" End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) Set mSh = Sh mShtName = Sh.Name End Sub Regards, Peter T "Tom L" wrote in message ... I've been searching for hours, can't find a solution but it's probably simple...how can I tell what sheet was the ActiveSheet BEFORE they changed to the current activesheet. Example: User is on a particular sheet (let's say they're on "Main"), then they click the tab to go to the "Prices" sheet. How can I tell what sheet they're comming FROM - in this case the "Main" sheet? In the Workbook and the Worksheet events Activate and Deactivate, I check the Sheet parameter (SH) and it always shows "Prices", the sheet they're going too. Thanks for your help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was ActiveSheet before changed to new sheet?
Hi Tom
Main sheet: right clik on tab - View Code and place below. Take a close look at the 2 arrows. Left: Object and right: Procedure. Option Explicit Private Sub Worksheet_Deactivate() MsgBox Me.Name End Sub Enjoy -- Best Regards Joergen Bondesen "Tom L" wrote in message ... I've been searching for hours, can't find a solution but it's probably simple...how can I tell what sheet was the ActiveSheet BEFORE they changed to the current activesheet. Example: User is on a particular sheet (let's say they're on "Main"), then they click the tab to go to the "Prices" sheet. How can I tell what sheet they're comming FROM - in this case the "Main" sheet? In the Workbook and the Worksheet events Activate and Deactivate, I check the Sheet parameter (SH) and it always shows "Prices", the sheet they're going too. Thanks for your help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What was ActiveSheet before changed to new sheet?
Thanks everyone for your code examples, it gave me the solution I needed!!
Tom "Tom L" wrote in message ... I've been searching for hours, can't find a solution but it's probably simple...how can I tell what sheet was the ActiveSheet BEFORE they changed to the current activesheet. Example: User is on a particular sheet (let's say they're on "Main"), then they click the tab to go to the "Prices" sheet. How can I tell what sheet they're comming FROM - in this case the "Main" sheet? In the Workbook and the Worksheet events Activate and Deactivate, I check the Sheet parameter (SH) and it always shows "Prices", the sheet they're going too. Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do some code after the sheet name is changed | Excel Discussion (Misc queries) | |||
Select the ActiveSheet & the sheet next to it | Excel Discussion (Misc queries) | |||
Check Activesheet for chart sheet or work sheet | Charts and Charting in Excel | |||
My ActiveX control does not respond when i draw it on a excell sheet, using ActiveSheet.OLEObject.Add | Excel Programming | |||
Copying new activesheet after other activesheet is hidden? | Excel Programming |