Consulting

Results 1 to 19 of 19

Thread: Back button

  1. #1
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    Back button

    You see what I want is a back button in case i forgot something on the prev sheet i was just on. Does anyone know how to do this?

  2. #2
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location
    Hi Daxton.

    I am sure (without a doubt) that I shamelessly lifted this from someone, somewhere...hope it gets you what you need:


    ' This code goes into the ThisWorkbook Module
    Option Explicit
    Public PrevWb As Worksheet
    
    Private Sub Workbook_Open()
        Set PrevWb = ActiveSheet
    End Sub
    
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        Set PrevWb = Sh
    End Sub

    And then this code goes into a (Standard) Module:

    Option Explicit
     
    Sub PrevWb()
        Worksheets(ThisWorkbook.PrevWb.Name).Activate
    End Sub



    This was tested and works in Office XP.

    Scott
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  3. #3
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    You can shorten:

    Worksheets(ThisWorkbook.PrevWb.Name).Activate
    To

    PrevWb.Activate
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  4. #4
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location
    Thanks for changing that Mark...I wouldn't have thought about it.

    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  5. #5
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    Talking ?

    It came up with a compile error in the module.
    It said:
    Compile Error:
    Method or Data Member Not Found

    And it had .PrevWB highlighted.
    When this does work, will it go back to previous workbook or worksheet?
    Because what I'm trying to do is go back to the previous worksheet. And I'm making this at work, so I was off yesterday is why it took me a while to respond.

    Thank You for all your Help Scott & Mark007

  6. #6
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Which line is this on?
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  7. #7
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    In the Module

    Sub PrevWb()
         Worksheets (ThisWorkbook.PrevWb.Activate)
    End Sub

  8. #8
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Use:

    PrevWb.Activate
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi,

    I tweaked the standard module code as so it won't error out if you haven't left the current sheet. Seems to work ok...

    Option Explicit
    Sub PrevWb()
        On Error GoTo nada
        Worksheets(ThisWorkbook.PrevWb.Name).Activate
        Exit Sub
    nada:
        MsgBox "You haven't left the current sheet yet."
    End Sub

  10. #10
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location
    Daxton and Mark,

    If I use the code as I first posted, SAVE the file, move to a new sheet and then run the macro I do go back to the sheet I was just looking at.

    If I use the code with the change to 'PrevWb.Activate' I get runtimes.
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

  11. #11
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    I get the same error Scott, I just left it the same with the one addition. Works like a charm for me.

  12. #12
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    ?

    It still gives me an error saying that .PrevWB can't be found. It says this exactly: Compile Error: Method or Data Member Not Found

    Is this b/c I'm using Office 2000 or is .PrevWB supposed to be standing for the workbook name like Worksheets(ThisWorkbook.Phonebook.Name)Activate

    And when I try to run another Module it says:

    Compile Error:

    Expected Function or Variable

    on the Workbook Module.

  13. #13
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Did you save the workbook w/ the code, then reopen it? It does have a workbook_open procedure called.

  14. #14
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    No I didn't

    But I just tried that and when I re-opened it, it gave me the error.

  15. #15
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Zip the file and post it.

  16. #16
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    ;)

    I'm sorry it took me awhile to respond, I was at lunch.

  17. #17
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Daxton,

    In your ThisWorkbook, find the part that says

    Option Explicit 
    Public PrevWb As Worksheet

    which you have between sheetactivate and sheetdeactivate, and move it to the very top of the module. Public variables have to be declared at the top of the module.

    Matt

  18. #18
    VBAX Contributor Daxton A.'s Avatar
    Joined
    Jun 2004
    Location
    Biloxi, Mississippi
    Posts
    143
    Location

    Thank You

    It works now.

    Thank All of you.

    This one is solved.

  19. #19
    Just A Dude VBAX Tutor Scottie P's Avatar
    Joined
    May 2004
    Location
    Remote from 18901 USA
    Posts
    263
    Location
    Hi Daxton.

    If you are good to go on this question I will mark it solved.

    Scott
    Life is Visual: Presence is Perception...
    How we see the world is how we respond to it. ~* Peace *~

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •