Return to previous workbook

  • Thread starter Thread starter Andy Sandford
  • Start date Start date
A

Andy Sandford

Hi

Any ideas on how I can write a macro to return to the previously active
workbook. I'd like the macro to run by hitting a button - pretty much like a
hyperlink, but not with an absolute reference.

I'm having trouble trying to figure out how to determine which workbook was
the last to be active before the currently active one...

If that makes sense!!

Cheers

Andy
 
Hi Andy

Sub test()
Dim Wbk As Workbook
Set Wbk = ActiveWorkbook
'whatever, and then
Wbk.Activate
End Sub
 
Hi Andy,

Here is one way.

Put this code in the ThisWorkbok code module

Public PrevWb As Worksheet

Private Sub Workbook_Open()
Set PrevWb = ActiveSheet
End Sub

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


and then add this code to a standard code module

Sub PrevWb()
Worksheets(ThisWorkbook.PrevWb.Name).Activate
End Sub

and link the PrevWb macro to your button.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob,

Took me ages to find you - I can't see my original post on the server for
some reason?!

I tried the code you suggested, but I can't get it to work yet...

If I open this workbook from the original source, there's no visible
effect - obviously if I hit the button without jumping from the source I get
an error - is there any way I "trap" this via a message box that says
something along the lines of "Source file not open"?

Thanks very much for your help

Andy
 
Andy,

Sorry mate, I was giving you a previous sheet solution, not a previous
workbook. Let's try again .

Firstly, all of this code goes in the source workbook.

'========================================
Insert a class module, and rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Public PrevWb As Workbook

Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
Set PrevWb = ActiveWorkbook
End Sub

'========================================
In a standard code module, declare a variable

Public PrevWb As Workbook


and add a macro which you assign to your toolbar button

Sub previous()
PrevWb.Activate
End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

Set PrevWb = ActiveWorkbook

End Sub

Hope that this is better.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob

Firstly thanks VERY much for your help!

I think I've got this right!

I pasted all of the code you gave me in the appropriate places in the source
workbook (say, book 1)

Works perfectly initially ..

But, if I make a copy of "book 1" and call it something else (say, book
2)...

I then open "book 2" and go via a link to the target workbook (book 3)...

....now when I hit the button in (book 3) to return to the previous workbook,
(book 2) I get an error.

I think this is because the button calls the macro in the original workbook
(book 1), which doesn't figure because it has never been opened - even
though all the code necessary is present in book 2 because it's an exact
copy of book 1.

My question is this - Firstly, did I put the code in the correct workbook
(book 1)? Or, secondly, how can I call the macro in each successive
workbook. There will be LOTS of copies of the original!

Am I making sense?! (confused myself!)

Cheers

Andy
 
Andy,

When you say that you create a copy, how is this done?

What about creating an addin for the code, and including the button build in
that? I'll start it whilst waiting for your answer.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hey Bob

The workbook is actually for costing the installation of AC equipment. This
workbook is a master copy - which has no user data entered as yet.

What I need to do is make copies of it, each of which is given a name
referring to one our clients. A1871,A1063 or A1234 etc. etc. for instance

Any of the workbooks can access a different (common) book "Equipment
costing" which returns some data. My button is on a worksheet on the
workbook "Equipment costing" - and I need to use it to return to the
spreadsheet that I jumped from to get to it.

e.g. I'm in "A1234" then I open "Equipment costing" when I need to return to
"A1234" I hit my button - if I was in A1063, I return to that.

Cheers Bob

Andy
 
Andy,

What I am suggesting is to divorce the code from your master workbook, there
seems little point in having multiple copies of the code, built-in
redundancy. In addition, I am suggesting adding a button to a toolbar, so it
is always available, and will not be dependent upon the master workbook
being open.

We could even create the master as a template, and have a button that does
the copy, thereby totally controlling it and overcoming those problems that
you are experiencing.

What do you think? And you didn't say how you currently make a copy. I need
to study your last post in conjunction with this to try and understand
exactly what happens.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello Bob,

Sorry - I didn't make it very clear...

What I'm currently doing, is opening the master and using "Save As" on the
File menu. Hence my problem.

When I access the workbook "Equipment Costing" from a copy of the master and
subsequently need to return to the copy I started from it all falls to
pieces...

In an Ideal world, I'd really like the button on the worksheet - rather than
the toolbar if it's poss.

I like the idea of a button on the master to make the copies - would it be
possible to lose that button on the resultant copies to avoid confusion?

The spreadsheet will be used by several people and needs to be pretty much
idiot proof - myself included!

Thanks a lot for your (continuing) help 8o))

Andy
 
Back
Top