back to previous active sheet ?

**Lukael** · 08-22-2014, 01:44 PM

Hi,

I'm listing through sheets and have Sheet2 for drop-down lists.

Sheet2 is not visible, but when I click on Cmdbutton It appears so that I can change values in drop-downs. When I hide Sheet2 again, I want to be back on sheet before Sheet2.

My code for open and close sheet2 :

Sub open_sheet()

Worksheets("Sheet2").Activate
Worksheets("Sheet2").Visible = True

End Sub

Sub close_sheet()

Worksheets("Sheet2").Visible = False
End Sub

How can I do that ?

**Jakobshavn** · 08-22-2014, 03:18 PM

You need to "remember" where you came from. In each sheet, except Sheet2, include the following event macro:

Private Sub Worksheet_Deactivate()
    Set Oldsheet = Me
End Sub

At the top of a standard module, include this single line:

Public Oldsheet As Worksheet

So whenever you move from worksheet to worksheet, OldSheet will remember where you came from.

Finally in a standard module:

Sub close_sheet()
    Worksheets("Sheet2").Visible = False
    Oldsheet.Activate
End Sub

**Lukael** · 08-22-2014, 04:45 PM

Hi Jakobshavn,

It works !

Thanks for help & explanation

back to previous active sheet ?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

back to previous active sheet ?

Re: back to previous active sheet ?

Re: back to previous active sheet ?

Thread Information

Users Browsing this Thread

Similar Threads

Macro to go back to the last active sheet and hide the active sheet

Moving invoice details from active worksheet to another sheet and back to the active one.

[SOLVED] Go back to previous active sheet (Excel 2007)

VBA code to open next sheet or go back to previous

Hyperlink refers back to previous sheet?

Bookmarks

Bookmarks

Posting Permissions