Prev: Excel ODBC driver created table/inserted data lost on application
Next: use Excel Now() in VBA
From: Spike on 19 May 2010 11:09
Does anyone have any idea how I can get the name of a worksheet that I have
just moved from. So if I am in Sheet1 and move to Sheet2, how do I get a
macro to return Sheet1. In all the Events I have looked at they all return
the name of the sheet that I have moved to not the sheet I have left.

Any help will be gratefully received

--
with kind regards

Spike
From: Gary''s Student on 19 May 2010 11:30
We need to remember it. In a standard module:

Public OldSheet As String
Public NewSheet As String

In the worksheet code area of every sheet:


Private Sub Worksheet_Activate()
If IsEmpty(OldSheet) Then
OldSheet = "x"
End If
MsgBox OldSheet
OldSheet = ActiveSheet.Name
End Sub

--
Gary''s Student - gsnu201003


"Spike" wrote:

> Does anyone have any idea how I can get the name of a worksheet that I have
> just moved from. So if I am in Sheet1 and move to Sheet2, how do I get a
> macro to return Sheet1. In all the Events I have looked at they all return
> the name of the sheet that I have moved to not the sheet I have left.
>
> Any help will be gratefully received
>
> --
> with kind regards
>
> Spike
From: Spike on 19 May 2010 12:22
That is grand, mnay thanks works a dream
--
with kind regards

Spike


"Gary''s Student" wrote:

> We need to remember it. In a standard module:
>
> Public OldSheet As String
> Public NewSheet As String
>
> In the worksheet code area of every sheet:
>
>
> Private Sub Worksheet_Activate()
> If IsEmpty(OldSheet) Then
> OldSheet = "x"
> End If
> MsgBox OldSheet
> OldSheet = ActiveSheet.Name
> End Sub
>
> --
> Gary''s Student - gsnu201003
>
>
> "Spike" wrote:
>
> > Does anyone have any idea how I can get the name of a worksheet that I have
> > just moved from. So if I am in Sheet1 and move to Sheet2, how do I get a
> > macro to return Sheet1. In all the Events I have looked at they all return
> > the name of the sheet that I have moved to not the sheet I have left.
> >
> > Any help will be gratefully received
> >
> > --
> > with kind regards
> >
> > Spike
From: Gary''s Student on 19 May 2010 12:36
Thank you for the feedback
--
Gary''s Student - gsnu201003


"Spike" wrote:

> That is grand, mnay thanks works a dream
> --
> with kind regards
>
> Spike
>
>
> "Gary''s Student" wrote:
>
> > We need to remember it. In a standard module:
> >
> > Public OldSheet As String
> > Public NewSheet As String
> >
> > In the worksheet code area of every sheet:
> >
> >
> > Private Sub Worksheet_Activate()
> > If IsEmpty(OldSheet) Then
> > OldSheet = "x"
> > End If
> > MsgBox OldSheet
> > OldSheet = ActiveSheet.Name
> > End Sub
> >
> > --
> > Gary''s Student - gsnu201003
> >
> >
> > "Spike" wrote:
> >
> > > Does anyone have any idea how I can get the name of a worksheet that I have
> > > just moved from. So if I am in Sheet1 and move to Sheet2, how do I get a
> > > macro to return Sheet1. In all the Events I have looked at they all return
> > > the name of the sheet that I have moved to not the sheet I have left.
> > >
> > > Any help will be gratefully received
> > >
> > > --
> > > with kind regards
> > >
> > > Spike
From: Mike Fogleman on 20 May 2010 06:41
Sub marine()
Dim prev As String

prev = ActiveSheet.Previous.Name
End Sub

Mike F

"Spike" <Spike(a)discussions.microsoft.com> wrote in message
news:E5AB538B-84C1-4513-9364-9743DCCA89C4(a)microsoft.com...
> Does anyone have any idea how I can get the name of a worksheet that I
> have
> just moved from. So if I am in Sheet1 and move to Sheet2, how do I get a
> macro to return Sheet1. In all the Events I have looked at they all
> return
> the name of the sheet that I have moved to not the sheet I have left.
>
> Any help will be gratefully received
>
> --
> with kind regards
>
> Spike

 | 
Pages: 1
Prev: Excel ODBC driver created table/inserted data lost on application
Next: use Excel Now() in VBA