PDA

View Full Version : Solved: A kind of Back button in sheet for other sheets



kishlaya
07-07-2009, 03:50 AM
Dear Friends
I need a lil help from you all. i have around 10 sheets and an 11th sheet called instructions. the sheet 'instructions' has all the info about the rest of the sheets(i.e like a help file). now i want a button to placed in each sheet which when clicked will take us to the 'instructions' sheet .till now no problem. now i want to give a 'back' button which will take it to the sheet from where it was revoked or activated. since this is dynamic so i cant give jus the sheet name.
Any help is appreciated.
Thanks in advance.:bow:
Regards,
Kishlaya

GTO
07-07-2009, 04:19 AM
Presuming an ActiveX button to be placed on "instructions", button code:

Private Sub CommandButton1_Click()
If Not strLastSheetName = vbNullString _
Then ThisWorkbook.Worksheets(strLastSheetName).Activate
End Sub

In the ThisWorkbook Module:
Option Explicit

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
strLastSheetName = Sh.Name
End Sub

In a Standard Module:

Option Explicit
Public strLastSheetName As String

Since the Deactivate event should be the last thing to occur before activating the Instruction sheet, this would seem dependable to me.

Hope that helps,

Mark

mdmackillop
07-07-2009, 04:26 AM
Here's a little Navigation Userform I use for larger workbooks. Search Navigate in the KB for some other items

kishlaya
07-07-2009, 04:41 AM
Dear GTO
Thanks a lot for your help, it works perfectly as desired.
Regards,
Kishlaya

kishlaya
07-07-2009, 04:42 AM
Dear Mdmackillop
Thanks for the useful sheet for navigation, i want to know how despite being a iserform active i am still able to work with my sheets, i was trying to that for a long time.

GTO
07-07-2009, 04:53 AM
Hi Kishlaya,

Very happy to help and you bet I'll be taking a gander at Malcom's wb later. It's nice to always be learning here, even with the amount of stuff that leaks out my brain nowadays...

If solved, you could mark it as such under Thread Tools right above your first post. It just saves others from checking unnecessarily.

Mark

mdmackillop
07-07-2009, 05:09 AM
Dear Mdmackillop
Thanks for the useful sheet for navigation, i want to know how despite being a iserform active i am still able to work with my sheets, i was trying to that for a long time.

That is a simple one; you need to open the form non-modal. The code for that is

Userform1.Show False

anandbohra
07-07-2009, 10:58 PM
Dear mdmackillop

in your given file navigate.xls

U had already added the option button
but if there is any method or something through which first we count the number of sheets in the file then add option button in the form & do your code.

as u have given provision for 25 option button instead in form load it count no of sheets then option button controls should gets added to form automatically.

JUST AN IMAGINATION OF MY MIND PL DO LET ME KNOW IF IT IS POSSIBLE OR NOT

mdmackillop
07-08-2009, 12:26 AM
I'm giving it some thought. Should be possible.