Solved How to close a workbook on the same sheet each time

March 12, 2018 at 07:46:48
Specs: Windows 10
I have a bit of a conundrum.

I want a workbook to close on the same sheet each time. I've already coded a Workbook_Open event to select a specific cell on a specific sheet so that when you open it, it will go to the right sheet. I'll use an example to demonstrate.

Let's say the workbook has Sheet 1 and Sheet 2. I always want users to see Sheet 1 when they open the workbook so I put in a Workbook_Open event in This Workbook to handle that. I then make some changes to Sheet 2 and save the workbook, then immediately share it via email as an attachment before closing it. When my colleague opens the workbook, they see Sheet 2 before clicking on Enable Content. Once they click enable content everything's fine as the Workbook_Open event is triggered and takes them to sheet 1.

I don't want to use the Before_Save event because less knowledgeable users might get confused as to why they're being taken to a different sheet after saving something.

I've trawled through Google but can't get the search terms right to find what I'm looking for. I'm sure there's a simple answer but annoyingly can't find it....

message edited by ScottV

See More: How to close a workbook on the same sheet each time

March 12, 2018 at 16:09:26
✔ Best Answer
That is quite a conundrum.

As far as I can tell, you need to switch to a specific sheet and the save the workbook in order for it to open to that sheet. That can easily be done with a Worksheet_Close macro, but it introduces a different problem:

Any "Close" will save the workbook whether the user wants to save changes or not.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Do the users need to see Sheet2 at all? You could Hide it and then save the workbook before you send it. You could make it xlVeryHidden so that users can't unhide via the UI.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •

March 13, 2018 at 01:22:44
To be honest Derby, I'm not too concerned. It's more of an aesthetic thing. I've developed a skills matrix and mentoring search tool. I shared it with a working group with all the sheets still visible. In the final version though, the file will be saved on a sharepoint site and all the stuff people don't need to see will be hidden along with all the tabs. Navigation will be accomplished through buttons on each sheet. With the workbook_open macro they'll always end up in the right place on opening. I'll just have to put a reminder on the sharepoint to tell people to always click enable content before use.

I'll have a think about the workbook_beforeclose idea and whether that causes me any headaches.

Thanks as usual.

Report •
Related Solutions

Ask Question