Simple Macro Question/Change on "Return"

Create a Public string variable in the module and set it to the name of the sheet from which the Workshop_Notes sub is called. Then use that name in the macro to return you to the sheet.

Here's an example.

In the Module:

Option Explicit

Public strSheetName As String

Select allOpen in new window

In Sheet9:

Private Sub Workshop_Notes_Click()
'This line must be before the Sheet9.Visible line
strSheetName = ActiveSheet.Name
'This hides Worksheet9

Sheet9.Visible = xlSheetHidden

Sheet11.Activate

End Sub

Select allOpen in new window

The macro:

Sub ReturnToSheet()
    Sheets(strSheetName).Visible = xlSheetVisible
    Sheets(strSheetName).Select
End Sub

Select allOpen in new window

Martinliss,

Thank you for the quick reply.  I followed your directions and have gotten an error back.   In working through the logic I may not have been as clear as I should have been.   After going to Sheet9, I want to return to the active sheet I was on prior to calling up Sheet 9.  I have multiple sheets that you can be on when you access Sheet 9.  Your comment;

"and set it to the name of the sheet from which the Workshop_Notes sub is called"  idicates to me that you want me to hardwire the return.

The error I'm getting is an Error9 "Script out of Range" and I get it on the "Sheets" line(s):

Option Explicit
    Public strSheetName As String
    Sub ReturnToSheet()
    Sheets(strSheetName).Visible = xlSheetVisible
    Sheets(strSheetName).Select

What am I doing wrong?

TY -- B

MartinLiss,

I see what you have done and it's clever.  However, I have 15 tabs that are open when you select "Notes".  So hiding them to "bring up" the Notes Tab won't work.  What is needed is to have the Notes Tab "hidden" until called from the Notes button.  The hidden when the "return" button is hit.  Now how do you go back to the original tab when you do that?

B.

I've incorporated the code into my wb but continue to get a debug "Script out of range" error when I fire the "return" macro.

Debug shows this line as the culpret:

    Sheets(strSheetName).Visible = xlSheetVisible

I have the code in the two WSs and the code in the Public Module.

B.

Upon further examination, you've used the buttons to code instead of assigning the Macros.  I don't know how to do that.  Can we do this with a simple button and assign the macro?

B.

Can you attach your workbook?

If the 'return' code (which is a macro) is not in a button, when would you like it run?

Put a breakpoint on the Sheets(strSheetName).Visible = xlSheetVisible line. What is the value of strSheetName?

The WB is too big to attach.

What I mean by the macro is in the button...... when I run a Macro, it's by creating a button and linking it.  It appears that you are using a .NET/VBA C++ button (is that right?) where the code is programmed into the button vs. being "linked".

??

B.

I went to Tools|Macro|Macros, entered the title ('ReturnToSheet') and clicked the 'Create' button (your Excel might be different). I then entered the code you see in post ID: 37779454. That macro gets saved in Module1 as a Sub that I call in my 'Return' button.

One question you didn't answer was "Put a breakpoint on the Sheets(strSheetName).Visible = xlSheetVisible line. What is the value of strSheetName?". Do you know how to do that? If not then take a look at my article on debugging. Don't worry that it's aimed at VB6.

I don't have "tools"; I'm running Excel 2010.  Where would I find it?  My question is, is this an ActiveX Control or a regular button?

I'll have to check out the article to set a breakpoint.  I'm still a novice ';)

It's a regular button.

Here's a link that explains where the Tools menu is.

Did that resolve your problem?

MartinLiss,

Thanks for waiting!  I got it to work as you described.  Very much appreciate your explanations and coaching.

All the best,

B.

You're welcome and I'm glad I was able to help.

My profile contains links to some articles I've written including one on debugging that may interest you.
Marty - MVP 2009 to 2012