I am trying to create a sheet with 'buttons' on, a bit like a 'linked table of contents', that will take the user to individual sheets (as opposed to clicking on the sheet tabs). I can see how to place Command buttons on a sheet (in this case Sheet1), but I think I need help with the code to make the buttons function as I envisage. Is this in the scope of help via this forum?
Command Button Link To Open Sheet(s)
-
-
-
Re: Command Button Link To Open Sheet(s)
Hi dejavajay,
It seems a strange way to do something, but the attached shows how to do it.
Bill
-
Re: Command Button Link To Open Sheet(s)
Turn on your macro recorder. Select a different sheet other than the one you're looking at. Stop your macro recorder. Now, from the Menu, go to View -> Toolbars -> Forms. Select the one with the button. Move your mouse where you want the button to appear. Left-click and hold down till you get the button to the desired size. Release. When you do, it will ask you which macro you'd like to assign it to. Assign it to the one you just created (Macro1, probably). When this is done, the button should still be highlighted. Go and change the text now to whatever you want. You're done! Repeat as needed. To view what you've recorded in the macro editor, simultaneously press ALT-F11.
I'm suggesting in the above that you use Form buttons, as they're easier to work with. You can use Command Buttons if you'd prefer. If invoking a Private macro, that may be the way to go.
-
Re: Command Button Link To Open Sheet(s)
Thank you for your prompt help. It's got me up and running.
-
Re: Command Button Link To Open Sheet(s)
Hi dejavajay,
You can also do it using hyperlinks. Add a command button from the Control Toolbar to the Menu sheet and double click it, then paste this code in.
Code
Display MoreDim wsht As Worksheet Dim i As Integer Dim sText As String Dim sName As String i = 1 Sheet1.Range("E10").Activate For Each wsht In ThisWorkbook.Worksheets If wsht.Name <> "MENU" Then sText = wsht.Name sName = wsht.Name & "!A1" Sheet1.Range("A" & i).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ sName, TextToDisplay:=sText i = i + 1 End If ActiveCell.Offset(1, 0).Activate Next wsht
EDIT: Clicking the command button whenever you add a sheet or a number of sheets to your workbook, will add a hyperlink to the sheets and add the hyperlink to the list of hyperlinks on the Menu sheet.
Bill
-
-
Re: Command Button Link To Open Sheet(s)
The work book will have more sheets than I can see on one screen, and I wanted a way to get them all listed and accessible from one page. Plus I wanted to see how to do it anyway. So thank you for your quick reply.
-
Re: Command Button Link To Open Sheet(s)
Hi dejavajay,
If you add the code from my last post into the Workbook_Open event, it will add the hyperlinks to all of the sheets in your workbook and list them on the Menu sheet.
Bill
-
Re: Command Button Link To Open Sheet(s)
Hi dejavajay,
The following code will add a list of hyperlinks to all sheets and also add a hyperlink on each sheet in Cell A1 to return to the Menu sheet. This code is placed in the Worksheet_Open event. This is much easier and cleaner that adding masses of command buttons to your sheets.
Code
Display MorePrivate Sub Workbook_Open() Dim wsht As Worksheet Dim i As Integer Dim sText As String Dim sName As String Application.ScreenUpdating = False i = 1 Sheet1.Activate Sheet1.Range("E10").Activate For Each wsht In ThisWorkbook.Worksheets Application.ScreenUpdating = False If wsht.Name <> "MENU" Then sText = wsht.Name sName = wsht.Name & "!A1" Sheets(sText).Activate ActiveSheet.Range("A1").Select Sheets(sText).Range("A" & i).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Menu!A1", TextToDisplay:="MENU" Sheet1.Range("A" & i).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ sName, TextToDisplay:=sText i = i + 1 End If Sheets("MENU").Activate ActiveCell.Offset(1, 0).Activate Application.ScreenUpdating = True Next wsht End Sub
Bill
EDIT: Sorry, can you hold back on this code for a while, it is putting the wrong hyperlink in the sheets for some reason. I will get back to you
-
Re: Command Button Link To Open Sheet(s)
I managed to get the problem solved via your earlier reply, but this one has me stumped. I pasted the code into a new Control Button as suggested, and changed the name of sheet 1 to MENU. Presumably that would be critical. But it isn't working for me. I an interested, as it sounds from your description that it will somewhat automate the process of adding the buttons and links. Care to keep trying with me?
-
Re: Command Button Link To Open Sheet(s)
Will wait. Thanks. DJ
-
-
Re: Command Button Link To Open Sheet(s)
Hi dejavajay,
I've fixed the code and attached a file with the code in both the Worksheet_Change event and the Command Button. If you add a sheet, then click the command button, it will add the hyperlink in the Menu sheet and a hyperlink in the new sheet to take you back to the Menu.
Bill
-
Re: Command Button Link To Open Sheet(s)
Hi Ranger, hope you are still active on this blog!!! While googling net to find the solution for creating hyperlinks for all the tabs which are provided in the specific range, i found ur reply. I guess you can help me. My problem is- i have set of tab names in a specific range (for eg E10 to E13 having XYZ, ABC, OPQ in each cell). All the XYZ, ABC & OPQ are the names of the tabs which are in the same workbook. So i need your help in creating hyperlinks for the text available in the cells E10 to E13 using a command button and the same way as given in the above code. Waiting for your reply. Thanks in advance.
-
Re: Command Button Link To Open Sheet(s)
Hey guys,
I'm sorry to bump such an old post but this is exactly what I'm looking for to work on a project for work, but I'm having a little bit of trouble with something that this doesn't cover.So, what I turned this into is: Two buttons that create sheets of specific names (ie. MM1 and SS1). One button creates @@ (example), one button creates ## (example) - the issue that arises is that when I open say; /Table of Content / @@1 / @@2 / @@3 / @@4 / ##1 / ##2 / ##3 / ##4 /, but then when I try to create an SS5 after making any @@s, the addlist populates the list and 'counts' the @@s in a strange way. The addlist hyperlink list will say "##1 ##1 ##1 ##1 ##1 ##2 ##3 ##4 ##5" because it counts the @@1-@@4 tabs on the bottom of the screen and plugs them in as ##, even though I 'thought' I had set it to look for "##" tab names only to be counted. I'm finding this to be hard to explain, my apologies if this is confusing..
Is there a way to get the addlist to ignore the @@s, and vice versa when creating @@s, the ##s?
The best I can do to explain this, I suppose, is to actually attach my file and show you first hand.Here is the file:
When you open it, you will see two Index tabs. Each has a button, each will add a sheet of a different kind.
To recreate my issue, go to one and add a few sheets.. You'll see the list appear.
Next, switch to the other Index and add a few of the other sheets. Notice at the bottom that they the second Index add placed it's sheets in front of the previous Indexes sheets.
Now, return to the first Index and add another sheet - this will recreate the problem, as it will add a bunch of hyperlinks that aren't real, because it's trying to take the second Indexes sheets into account and I don't know why.I hope that someone can help me out with this. Again, sorry for my confusing explanation!
Thanks!
Helzehenforum.ozgrid.com/index.php?attachment/51807/
Sorry for the .rar, my 2 page excel file was 267kb and it only allows me to upload 100kb or so.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!