How do I know which sheets are to the RIGHT and LEFT to the Active Sheet?

Hi e4excel,
You are asking to different questions.
If you have different "Workbooks" then these are different files on your harddrive or server.
If you have different "Worksheets" then these are different tabs in a single workbook file.

You have mixed these words in your question above.

If you meant different TABS on a single WORKBOOK then you can use an index number to address the different Tabs or Worksheets.

Worksheets(1) would be the first tab, on the very left of a workbook
Worksheets(Worksheets.Count) would be the last tab, on the very right of the workbook.

hope this helps.

To answer the thread title is this. Your current worksheet number is "Activesheet.Index". So to access the sheet to the left is Worksheets(Activesheet.Indes - 1). And + 1 to get the worksheet to the right.

You could assign these two macros to shortcut keys (i.e. Ctrl+b and Ctrl+f ) so you could key your way thru the various sheets. You could also have a "Ctrl + i" that would activate the "index" sheet.

Public Sub forward()
   On Error GoTo FirstSh
   Sheets(ActiveSheet.Index + 1).Activate
   Exit Sub
FirstSh:
    Sheets(1).Activate
End Sub

Public Sub backward()
   On Error GoTo LastSh
   Sheets(ActiveSheet.Index - 1).Activate
   Exit Sub
LastSh:
    Sheets(Sheets.Count).Activate
End Sub

Originally Posted by MarvinP

Hi e4excel,
You are asking to different questions.
If you have different "Workbooks" then these are different files on your harddrive or server.
If you have different "Worksheets" then these are different tabs in a single workbook file.

You have mixed these words in your question above.

Yes I did not realise that ..Yes I meant WorkSheets and not different WorkBooks..

Sorry about that!

Per your suggestion..

So to access the sheet to the left is Worksheets(Activesheet.Indes - 1). And + 1 to get the worksheet to the right.

What do I write and where?

Like I was thinking of having 3 Hyperlinks in 3 Fixed Cells in the different SHeets to manoeuvre the Movement..

So please tel me if the cells are A1,A2 and A3 what do I do and which event...

I want these to show up in every sheet with the functionality to move to previous, next and Index Sheet..

Thanks a lot for replying..

Warm Regards
e4excel

Another way:

?activesheet.name
Sheet2
?activesheet.previous.name
Sheet1
?activesheet.next.name
Sheet3
?sheets("sheet2").previous.name
Sheet1

Regards

    ActiveSheet.Hyperlinks.Add _
        Anchor:=Range("A1"), _
        Address:="", _
        SubAddress:=ActiveSheet.Previous.Name, _
        TextToDisplay:="Go to " & ActiveSheet.Previous.Name

Regards

Hi,

Find the attached sheet with macros and QAT buttons to do as you now have explained.

Your last post implied you wanted to add a new worksheet after the last. That was new to the original question. I haven't included code to do this.

Hi e4excel,
The QAT is better known as the Quick Access Toolbar and are the Icon's above the Menu Bar on top of an Excel Window of File, Edit, View, Insert etc. See the picutre at http://www.rondebruin.nl/0307commands.htm

I followed instructions given on this page: http://www.rondebruin.nl/imageqat.htm

Above the word Insert on my QAT are now 2 new button Icons. One with a left pointing arrow, and one with a right pointing arrow. The code for these two buttons is in the Module in the VBA code of the attached workbook attached above.

The way to do this is to first create a subroutine and then attach a QAT to the routine using the Custom Dropdown from the link above. You can see the code by opening the Visual Basic Viewer on the Developer Tab of your Excel program.

I hope this lets you see how I would go about what I thing you are asking for. That was writing code that would go to the previous and next tab in a workbook, that would be available from any worksheet.

Is that what you final question was?

Hello e4excel
I'm really not understanding how having to scroll to A1 on a sheet and click an arrow is faster than excels built in methods of
A) clicking on sheets tab that requires no scroll (i.e. next sheet)
B) rt clicking on the bottom bars arrows and selecting from popup sheet list

However I can see someone might want to use shortcut keys versus mousing sooo I've included sample of my ealier suggestion i.e.
Ctrl + i = goto Index sheet
Ctrl + f = Forward to next sheet
Ctrl + b = Back one sheet

BY-THE-WAY:
You do know there is a built in Index of sheet ? i.e. if you right-click over the arrows in the lower left corner of excel sheet a sheet index will popup

EXCELS DEFAULT PAGE NAVIGATION
Move to the next sheet in the workbook. CTRL+PAGE DOWN
Move to the previous sheet in the workbook. CTRL+PAGE UP
http://office.microsoft.com/en-us/ex...005203781.aspx

Note that Ctrl-B and Ctrl-I are, by default, the shortcut keys for bold font and italic font respectively (across all Office applications). Your users may find this confusing if they use those shortcuts.

Regards

Hey e,

Nimrod is absolutely correct . Excel has most everything we need already and we as humans simply need to find how it does our work for us.

A few years back I needed to filter a pivot table for a user programatically as he felt the Excel way was too complicated. I created this user interface instead of teaching him how to use the normal pivot table dropdown filters. It took days to figure out the code and the lists and the code. Now that he's learned how to use the standard method, he has a better understanding of how it works and more capabilities that I every wrote code for.

YOU NEED to use the standard method of simply clicking on the tabs at the bottom of the workbook to change tabs. Left and Right become obvious when you can see which tab you are on. Why do you want to create another method/standard for doing something easy in excel when it is already there and easy to learn or teach.

Dear NimRod and MarvinP,

I respect your views and completely agree with you that this is too simple a request to move between the tabs but the reason of asking for its is to Save the Time by taking the pointer to always a particular cell from where there the person can take control of the Sheet for Printing the Sheets contents which are already formatted for the Print Preview..

Hope you understand..!

I have tried to make up some code of my own with great help from the Forum and TMShucks inital code however theres something which is missing as I am getting an error mentioning that "REFERNCE IS NOT VALID"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Range("e1").Value = "Go to " & ActiveSheet.Previous.Name
If Target.Address = "$E$1" Then
'Target.Value = Time
ActiveSheet.Hyperlinks.Add _
Anchor:=Range("E1"), _
Address:="", _
SubAddress:=ActiveSheet.Previous.Name, _
TextToDisplay:="Go to " & ActiveSheet.Previous.Name


End If
End Sub

Would appreciate if someone could just tweak a little..
I think instead of the "ActiveSheet.Hyperlinks.Add _" it needs to be the Previous SHeet...


Warm Regards
e4excel

Hi e,

I find this thread interesting. You ask how to know sheets Right or Left. We (a few of us) answer and you don't like our answers, because you want us to use Hyperlinks in our answer. I guess you want us to use Hyperlinks so you can land on the same cell when moving from sheet to sheet. Perhaps the Title needed to be. "How to create a hyperlink on sheets to move to sheet (Left or Right) and get to common cell".

All we can do is try to answer your question based on our best experience. If you need to have it done in a very specific way using specific code, you might be looking for a contract programmer.