Solved

Define a sheet name to be returned to later.

Posted on 2011-02-25
8
207 Views
Last Modified: 2012-05-11
I need to leave a current sheet and return to it. How do I set this up? I have tried many different ways, but can't seem to get it to work.
Dim CurSht As Worksheet
  
  Set CurSht = ActiveSheet
   MsgBox "current sheet=" & CurSht

Open in new window

0
Comment
Question by:CRHIV
8 Comments
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 25 total points
ID: 34981654
You can do it with Ctrl+PgUp and Ctrl+PgDown
also, the code you wrote is not good...
it should be like this, but anyway, it doesn't do what you want it to do... it just pops the activesheet's name...
Dim CurSht As Worksheet
  
  Set CurSht = ActiveSheet
   MsgBox "current sheet=" & CurSht.Name

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34981819
to return to it later...


Dim CurSht As Worksheet
  
  Set CurSht = ActiveSheet

'more code

CurSht.Select

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 25 total points
ID: 34981826
A little better:



Dim CurSht As Worksheet
  
  Set CurSht = ActiveSheet

'more code

With CurSht
    .Parent.Activate
    .Select
End With

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:CRHIV
ID: 34983262
I saved the variable CurSht to another sheet that the user has to check for changes, then send to the printer. I can't get the program to recognize the CurSht variable again.
Unload Me
        Sheets("napa").Select
        Sheets("NAPA").Range("K23") = CurSht
End Sub




Sub Rectangle124_Click()
    Dim CurSht As String
    Dim STDprinter As String
    CurSht = Range("K23").Value
    STDprinter = Application.ActivePrinter
    Application.ActivePrinter = "OKI MICROLINE 1120 on Ne00:"
    Sheets("NAPA").PrintOut
    Application.ActivePrinter = STDprinter                'change back to standard printer
   MsgBox "Current Sheet=" & CurSht
    Sheets(CurSht).Select

End Sub

Open in new window

0
 

Author Comment

by:CRHIV
ID: 34983797
After working with it some more, It's picking it up. But the code seems to stop after printing the document.
When it gets to the Rectangle124_click(), I don't get any MsgBox replies.
Unload Me
        Sheets("napa").Select
        Sheets("NAPA").Range("K23") = CurSht
        CurSht = ""
        MsgBox "cursht=" & CurSht
        
        CurSht = Range("K23")

        MsgBox "CurSht=" & CurSht

End Sub

'Does not work below here, except for the print


Sub Rectangle124_Click()
    Dim CurSht As String
    Dim STDprinter As String
    CurSht = Range("K23")
    STDprinter = Application.ActivePrinter
    Application.ActivePrinter = "OKI MICROLINE 1120 on Ne00:"
    Sheets("NAPA").PrintOut
    Application.ActivePrinter = STDprinter                'change back to standard printer
    MsgBox "CurSht=" & CurSht
    Sheets("CurSht").Select
    MsgBox "CurSht=" & CurSht
    Sheets("Sheet7").Select


End Sub

Open in new window

0
 

Author Comment

by:CRHIV
ID: 34984799
Never mind, Had the sub in two places.

Thanks for your help.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35225362
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

10 Experts available now in Live!

Get 1:1 Help Now