Sheet name problem

[Guest]

Hi,

I am developing a system where charts are used to display data. Two or more
sheets within the system can go to the a chart. The problem is that I cannot
get the back button to go to the previous sheet on which the user was on. I
have copied the chart sheets to change the code but this does not solve the
problem as new sheets are created with different sheet names so there is no
way to no future sheet names. Is there a way of remembering the sheet before
going to a chart and then being able to return the previous sheet.

I dimed a variable called lastsheet and tried activesheet=lastsheet but I
couldn't get the back button to recognise lastsheet as a sheetname.

Many thanks for your help

Nathan

 

[Ron de Bruin]

Use
lastsheet = activesheet

And this to select it for example
lastsheet.select

 

[JE McGimpsey]

Try:

Dim wsLastSheet As Worksheet

Set wsLastSheet = ActiveSheet
'Do your chart stuff here
wsLastSheet.Activate

 

[Bob Phillips]

Nathan,

Put this code in ThisWorkbook

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set PrevSheet = Sh
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


and then in a general module add this code to support the Back function

Public PrevSheet

Sub Back()
Application.EnableEvents = False
PrevSheet.Activate
Range("A1").Activate
Set PrevSheet = ActiveSheet
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron de Bruin]

You see that I for get the "set "in my example before lastsheet
See Bob's and J.E's reply