Simple Macro Question/Change on "Return"

EE Professionals,

I have a worksheet (Worksheet9) that I want to "call up" via a button from any screen in my WB.  I also have a button on the worksheet called up (i.e. Worksheet9) that when the macro is fired, I want it to return me to whichever WS I was on when I originally called it up.

I have placed the macro in a public Module (maybe it needs to be in This Workbook).  But I don't want to go to Sheet 11..... I want to go back to whichever sheet I was originally on.

Sub Workshop_Notes_Click()
'This hides Worksheet9
Sheet9.Visible = xlSheetHidden
Sheet11.Activate
End Sub

So;

1.) What do I replace Sheet11 with in order to do that?
2.) Where do I put the Macro? (Module, Worksheet or This Workbook)?

Thank you in advance,

B.
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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.
0
Martin LissOlder than dirtCommented:
Here's an example.

In the Module:
Option Explicit

Public strSheetName As String

Open 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

Open in new window

The macro:

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

Open in new window

0
Bright01Author Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Martin LissOlder than dirtCommented:
Here's a working example.
WorkShop.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
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.
0
Bright01Author Commented:
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.
0
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
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?
0
Bright01Author Commented:
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.
0
Martin LissOlder than dirtCommented:
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.
0
Bright01Author Commented:
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 ';)
0
Martin LissOlder than dirtCommented:
It's a regular button.

Here's a link that explains where the Tools menu is.
0
Martin LissOlder than dirtCommented:
Did that resolve your problem?
0
Bright01Author Commented:
MartinLiss,

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

All the best,

B.
0
Martin LissOlder than dirtCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.