Solved

Simple Macro Question/Change on "Return"

Posted on 2012-03-28
15
142 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Bright01
  • 8
  • 7
15 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37779355
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37779454
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
 

Author Comment

by:Bright01
ID: 37781018
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 37782123
Here's a working example.
WorkShop.xls
0
 

Author Comment

by:Bright01
ID: 37786609
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
 

Author Comment

by:Bright01
ID: 37786639
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
 

Author Comment

by:Bright01
ID: 37786722
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 37787324
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
 

Author Comment

by:Bright01
ID: 37788145
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37788281
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
 

Author Comment

by:Bright01
ID: 37788406
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37788531
It's a regular button.

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

Expert Comment

by:Martin Liss
ID: 37792169
Did that resolve your problem?
0
 

Author Closing Comment

by:Bright01
ID: 37849059
MartinLiss,

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

All the best,

B.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37849099
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now