Sheet Navigation

Hi,

I have been looking around the forums but cant find much to assist
Please can you help.

I have a wide selection of sheets and would like the user to be able t
click onto them whenever he/she decides. How is this done? VBA?

Thanks

Andre

            Attachment filename: test navigation.xls           
Download attachment: http://www.excelforum.com/attachment.php?postid=55558
--
Message posted from http://www.ExcelForum.com

0
5/25/2004 12:49:04 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
670 Views

Similar Articles

[PageSpeed] 54

You could try naming areas of the sheet by highlighting a cell or group of
cells and then naming it/them by clicking in the name box?

Cheers from North Yorkshire UK


John
"ajw150 >" <<ajw150.16tbbq@excelforum-nospam.com> wrote in message
news:ajw150.16tbbq@excelforum-nospam.com...
> Hi,
>
> I have been looking around the forums but cant find much to assist.
> Please can you help.
>
> I have a wide selection of sheets and would like the user to be able to
> click onto them whenever he/she decides. How is this done? VBA?
>
> Thanks
>
> Andrew
>
>             Attachment filename: test navigation.xls
> Download attachment:
http://www.excelforum.com/attachment.php?postid=555582
> ---
> Message posted from http://www.ExcelForum.com/
>


0
jp1 (93)
5/25/2004 12:56:12 PM
what u can do is on that specific cell---do a right click , choos
insert hyperlink and then on the icons coming on the left side ,clic
on the place in this folder document----and specify a cell number lik
B50

what will happen is if someone clicks tht cell it will take him to cel
B5

--
Message posted from http://www.ExcelForum.com

0
5/25/2004 1:04:33 PM
I was hoping to be able to list the titles in a dropdown box of som
sort, to make it look neater, and then allow the user to click on tha
link. They then get taken to the sheet.

Andre

--
Message posted from http://www.ExcelForum.com

0
5/25/2004 3:11:15 PM
Andrew

If you right-click on the navigation arrows at bottom left of Status bar you
get a list of worksheets to select from.

I believe 16 will be shown but is scrollable.

OR you could use David McRitchie's TOC code from

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

OR Bob Phillips' BrowseSheets from

http://snipurl.com/6nur

Gord Dibben Excel MVP

On Tue, 25 May 2004 10:11:15 -0500, ajw150
<<ajw150.16thwp@excelforum-nospam.com>> wrote:

>I was hoping to be able to list the titles in a dropdown box of some
>sort, to make it look neater, and then allow the user to click on that
>link. They then get taken to the sheet.
>
>Andrew
>
>
>---
>Message posted from http://www.ExcelForum.com/

0
Gord
5/25/2004 7:54:53 PM
One more option:

http://groups.google.com/groups?threadm=40A584A3.DB381C74%40msn.com

"ajw150 <" wrote:
> 
> I was hoping to be able to list the titles in a dropdown box of some
> sort, to make it look neater, and then allow the user to click on that
> link. They then get taken to the sheet.
> 
> Andrew
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/25/2004 11:52:54 PM
Sorry Dave I couldnt get the link to work.

Andre

--
Message posted from http://www.ExcelForum.com

0
5/26/2004 7:16:53 AM
I clicked on it and it took me to that thread in google.

Here's a copy of the post:

How about a floating toolbar that displays all the worksheet names?

The default behavior when you type into the combobox on that toolbar is to match
the entries.


Option Explicit
Sub auto_close()
    On Error Resume Next
    Application.CommandBars("MyNavigator").Delete
    On Error GoTo 0
End Sub

Sub auto_open()

    Dim cb As CommandBar
    Dim ctrl As CommandBarControl
    Dim wks As Worksheet
    
    On Error Resume Next
    Application.CommandBars("MyNavigator").Delete
    On Error GoTo 0
    
    Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
    With cb
        .Visible = True
        Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
        With ctrl
            .Style = msoButtonCaption
            .Caption = "Refresh Worksheet List"
            .OnAction = ThisWorkbook.Name & "!refreshthesheets"
        End With
        
        Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
        With ctrl
            .Width = 300
            .AddItem "Click Refresh First"
            .OnAction = ThisWorkbook.Name & "!changethesheet"
            .Tag = "__wksnames__"
        End With
    End With

End Sub
Sub ChangeTheSheet()

    Dim myWksName As String
    Dim wks As Worksheet

    With Application.CommandBars.ActionControl
        If .ListIndex = 0 Then
            MsgBox "Please select an existing sheet"
            Exit Sub
        Else
            myWksName = .List(.ListIndex)
        End If
    End With
        
    Set wks = Nothing
    On Error Resume Next
    Set wks = Worksheets(myWksName)
    On Error GoTo 0
    
    If wks Is Nothing Then
        Call RefreshTheSheets
        MsgBox "Please try again"
    Else
        wks.Select
    End If
    
End Sub
Sub RefreshTheSheets()
    Dim ctrl As CommandBarControl
    Dim wks As Worksheet
    
    Set ctrl = Application.CommandBars("myNavigator") _
                      .FindControl(Tag:="__wksnames__")
    ctrl.Clear
    
    For Each wks In ActiveWorkbook.Worksheets
        ctrl.AddItem wks.Name
    Next wks
End Sub


If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



"ajw150 <" wrote:
> 
> Sorry Dave I couldnt get the link to work.
> 
> Andrew
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/26/2004 11:46:22 PM
Reply:

Similar Artilces:

How can I share information between multiple sheets in Excel?
New question: I know now how to use "=sheet1!a1" to copy cells from sheet 1 to sheet 2,3 etc. Is there is a formula that will allow me to enter data into sheet 2, and have it copy automatically into sheet 1 and 3, or into sheet 3 going to sheet 1 and 2? So that I can enter the data into any sheet and have it transfer to all sheets, rather than always from sheet 1 to sheets 2 and 3. Nothing built into excel (except for the formula you showed). But I'd be very hesitant to separate my data. I'd do my best to keep all the data on one worksheet. You can use Data|Fi...

Transfer the total of mutiple entries in a sheet to another sheet in the same file #2
How to transfer the total of multiple of multiple entries in a sheet to another shet in the same file. Please help me in this formula ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** ...

Call from a different sheet?
I'm wondering if it's possible in Excel to call data from a different sheet. For example if I changed A1 in sheet 2 it'd affect forumlas in sheet 1. -- HisHighness ------------------------------------------------------------------------ HisHighness's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31454 View this thread: http://www.excelforum.com/showthread.php?threadid=511552 You can link to another worksheet with a formula like ='Sheet1'!A1 -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson...

HOW DO I CHANGE THE ORDER OF THE SHEETS IN EXCEL WORKBOOK?
my sheets are not in the order I want them. How do I change the order. The data is already in the sheets. Click on the tab and drag it to where you want to. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------�------------------------------�---------------- "joanbaer" <joanbaer@discussions.microsoft.com> wrote in message news:1F45...

Max number sheets in a file?
How many sheets can an excel 2002 file have if each sheet has only abou a page of text? I'm just looking for a general idea, if it's lots an lots and I don't need to worry about it that's a good enough answer fo m -- Message posted from http://www.ExcelForum.com The number will be limited by the memory in your computer. Certainly hundreds of sheets is well within most machine's capacity. In article <bishopwill.154u1g@excelforum-nospam.com>, bishopwill <<bishopwill.154u1g@excelforum-nospam.com>> wrote: > How many sheets can an excel 2002 file hav...

Sheet to sheet copying
Hi, I have two spreadsheets. One called nursing and the other medical. I want to be able to copy certain cells from one sheet to other and visa verca without the need to copy & paste. So I want a cell in nursing to search a column for specific text in the medical sheet, then I want the contents of cells in the row where the text is found to be copied to cells in the nursing sheet. Example: The cells B3-G3 in the nursing sheet to search Column A in the medical sheet for specific text e.g. "G1". When "G1" is found in cell A5 then the contents of cells B5-G5 in t...

Check Register to Mortgage Sheet
Hi; I am using MS Money 2007. If I write a check in my register to my mortgage company, how do I get that transaction to update my mortgage account register? Do I have to type it in twice (Once to my check register and once to my mortgage register)? I would like to set some stuff up where items in my check register automatically cary to other account items. Thanks for any advice on this. Pat From the Check Register make it a transfer to your Mortgage Account. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mn...

list sheet comments
Hi, I have a sheet with a lot of comments. I would like to create a macro that lists all the comments of the sheet in column F. I=B4ve tried something similar like this: For Each cmt in ActiveSheet.Comments MsgBox cmt.Text Next cmt But I don=B4t know how to list each comment in each cell of column F instead of a msgbox. thanks Dim StartCell as Range dim Cmt as comment with activesheet set startcell = .range("F1") for each cmt in .comments startcell.value = cmt.txt 'come down to the next cell set startcell = startcell.offset(1,0) n...

blank out rest of work sheet
I was wondering how to blank out the empy cells in my worksheet. For example: I have data in columns A-F down about 50 rows. I do not want to see any rows past that. When I am saving the data as a text file, I am getting extra rows in my file for some reason and I want to see if this would prevent this from happening. Thanks... --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.769 / Virus Database: 516 - Release Date: 9/24/2004 Hi Shawn, See the explanation and remedial notes on Debra Dalgleish's web site: ...

No Bound Sheets Found Error Dialog Box-Excel 2000
Hello all. I have a 8.5MB spreadsheet that is the summary of weekly timesheets that are Excel files. I have attempted various strategies to reduce the size of the files. This sheet is all references to other worksheets. Up until two days ago, when opening the file and it asks, this contains references to other files...do you want Excel to update the cell refences, y or N. I always hit okay and it does it's stuff. Now however, when the box comes up when opening the file, I hit okay and it returns a "No Bound Sheets" error dialog box. All I can do is hit okay and a...

How to populate one sheet with data from another sheet
I have 2 sheets in the excel file. On the first sheet the first colum is a part num only. The second sheet has part num on the first column and descriptions on the second column. I need a macro or script that will scan the second sheet for part num that matches the first sheet part num and populate the description(at the second column) on the first excel sheet. -- KH One way .. Assuming the reference data is in Sheet2, cols A and B col A = part#, col B = description In Sheet1 ----------- With col A containing part# in A2 down, Put in B2: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"&quo...

Changing from sheet to sheet
Hi there! Is there a function that would enable me to navigate from sheet to sheet in an Excel document (instead of using the mouse and click on the sheet I want to work with)? Many thanks! Hi Ctrl PgUp and Ctrl PgDwn is one way. Insert > Hyperlink (and after that clicking it) is another. HTH. Best wishes Harald "Greywolf" <anonymous@discussions.microsoft.com> skrev i melding news:5bfc01c42d6b$c2a26630$a101280a@phx.gbl... > Hi there! > > Is there a function that would enable me to navigate from > sheet to sheet in an Excel document (instead of using the &...

Delete values in sheet 2 that arre found in sheet 1
Is there an easy way to delete values in sheet 2 that match the value from sheet 1. I have approximately 10000 rows in sheet 1 and some identical rows on sheet 2 that need to be eliminated? I not sure if this has to be done pragmatically or if there is some feature in excel 2007 that can handle this type of request. thanks Nick If it is the values in a single column on each sheet that make up the duplication, it's fairly easy. Lets say you want to compare the contents of column A on each sheet to find duplicates on Sheet2 (that are also on Sheet1). Your data starts o...

Data navigator control
I am looking for some sample code for developing an activeX control that would allow the user to move forward and backward through data. ...

Function to Copy all sheets into one sheet
Is there a way to copy multiple sheets at once to create one worksheet, rather than one at a time? You could use a for/each loop for each ws in worksheets depends on what and where you want to copy next -- Don Guillett SalesAid Software donaldb@281.com "Des" <cusaty@express-scripts.com> wrote in message news:013801c3b43a$5cb403b0$a101280a@phx.gbl... > Is there a way to copy multiple sheets at once to create > one worksheet, rather than one at a time? ...

Linking sheets
I am trying to hyperlink a cell in one single sheet spreadsheet to a specific sheet in another multisheet book. I don't need to get to any specific cell in the second spreadsheet, just the specific sheet. Thx, Chris In Excel 2003 or earlier. Insert>Hyperlink>Existing file or webpage. Browse to the target workbook then hit "Bookmark" and pick a sheet and a cell. Gord Dibben MS Excel MVP On Thu, 22 May 2008 19:14:38 -0700 (PDT), spacia <chris_butler@thegrid.net> wrote: >I am trying to hyperlink a cell in one single sheet spreadsheet to a >specific sheet in...

Saving Excel Sheet
I have a series of worksheets that I use to generate a quotation. I would like to use these worksheets like a template but when saved I only want to save the results in a separate file is this possible? If you look up Template in Help there is quite a good explanation of how to create and use a template. -- Regards, OssieMac "dragons_lair" wrote: > I have a series of worksheets that I use to generate a quotation. I would > like to use these worksheets like a template but when saved I only want to > save the results in a separate file is this possible? ...

move to other sheet
I would like to move certain rows from one sheet to another. For example: If there are in column A cells containing "DC BMW" move the row that goes with it to sheet named DC BMW. Fen Dag fen Start here if you want to do it with VBA code http://www.rondebruin.nl/copy5.htm Groeten Ron -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fen" <f.klompsma@upcmail.nl> wrote in message news:59225$4bb37f3b$d55d9af3$26184@news.chello.nl... > I would like to move certain rows from one sheet to another. > For example: If there a...

Re: Unprotecting Sheets
Is it possible to unprotect all sheets in a workbook at the same time? Zee, You could use VBA to cycle through all the sheets that are protected and unprotect them that way. Don't think it is possible without code. -- http://www.ExcelHelp.us ed@ExcelHelp.us 888-MY-ETHER ext. 01781474 "Zee" wrote: > Is it possible to unprotect all sheets in a workbook at the same time? Unfortunately I am not very familiar with VBA and therefore am not sure what to enter into the editor to accomplish this. "galimi" wrote: > Zee, > > You could use VBA to cycle thr...

how do I create a spread sheet to automatically calculate when I
On a new spread sheet I am entering numbers and I would like them to be multiplied and changed automatically. The formula is x*3 then less 40%. Can anyone help me. One way: If you are entering your numbers in column A, say, then in column B enter: B1: =(A1*3)*(1-40%) or, better, B1: =A1*3*60% copy B1 down as far as necessary. In article <678FA28E-14A7-40E5-B0DF-54D574F9F7E3@microsoft.com>, wbinfo <wbinfo@discussions.microsoft.com> wrote: > On a new spread sheet I am entering numbers and I would like them to be > multiplied and changed automatically. The f...

moving from sheet to sheet
I need to write code that automatically moves from sheet to sheet and gathers data. I have no problem gathering the data on the sheet but I don't know how to move the pointer to the next sheet in the workbook. I would really appreciate any help you can give. -- Bob A Sub moveSheetToSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets MsgBox ws.Name Next End Sub "Bob A" wrote: > I need to write code that automatically moves from sheet to sheet and gathers > data. I have no problem gathering the data on the sheet but I don't know h...

Persistent References to Data in other Sheets
Is it possible to have a cell in a worksheet reference a cell in another worksheet in the same workbook, and if the referenced cell is deleted, reference the new cell, rather than #REF For examle, if I have something in Cell A1 on Sheet1, I can use =Sheet1!A1 on Sheet2 to display the contents of the cell. However, when I delete Row A on Sheet 1, so I can put new data into it, my reference changes to =Sheet1!#REF How can I force the reference to stay =Sheet1!A1 "Cyndrax" <anonymous@discussions.microsoft.com> wrote in message news:4E408DC8-803B-47AE-8320-C6B8A561B22C@microsof...

How can I use conditional formating between two sheets in one work
If employee number 5263 found in sheet 2, show the raw in main data on sheet 1 in red, for instance. ok this is how i did it use a helper column and a vlookup formula back at sheet 2 =IF(VLOOKUP($G$11,Sheet2!$A$2:$A$8,1,FALSE)=A8,"found","") so if employees name is found on sheet 2 this formula returns found on the same row as the emplyees name conditional formatting for this row is =c8="found" i had to change the formatting formula for each row,hopefully there is an easy way! -- paul paul.shepherd@nospamparadise.net.nz remove nospam for email addy! "...

pushbutton to print all sheets
Hi, I would like to have a pushbutton on the first sheet so I can print all sheets of the current workbook... How can this be done?? Thanks JP Hi assign the following macro to a button: sub foo() activeworkbook.printout end sub -- Regards Frank Kabel Frankfurt, Germany "Jean-Paul De Winter" <jp.dewinter@pandora.be> schrieb im Newsbeitrag news:90a3e4cc.0404051353.42615847@posting.google.com... > Hi, > I would like to have a pushbutton on the first sheet so I can print > all sheets of the current workbook... > How can this be done?? > Thanks > JP ...

Creating a copy of a sheet and naming it
Being a newbie to VBA could I please have some advice . What I am trying to do is write a macro to select the active sheet and create a copy of it but then I would like an input message to ask me what to name the sheet so that I am presented with an exact copy (including macro buttons) but with a name of my choice. I have tried a myriad of combinations but do not seem able to get it to work This macro must be able to work with any selected sheet in the workbook Any help would be appreciated Brian Scotland Brian, SheetName = InputBox("Enter desired name for copy of active sheet&quo...