LinkBack URL
About LinkBacks
Hi,
I want to a dropdown macro on the first tab to allow users to jump to other tabs.
Each other tab will be a month of the year.
Thank you
Hi,
I want to a dropdown macro on the first tab to allow users to jump to other tabs.
Each other tab will be a month of the year.
Thank you
You already have that functionality within Excel. Right-click on the sheet navigation icons in the bottom left corner of the file window, and you will see a list of all sheets within the file. Just click on the one you want to go to, and hey presto!
Hope this helps.
Pete
Generally speaking you'd use Worksheet_Change event.
Assuming that you have data validation dropdown in A2 of first sheet.
Please Login or Register to view this content.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
https://www.excelforum.com/excel-pro...t=#post4983807
Have a look at a form that lists the sheets - double clicking navigates to sheet
Edit: Wrong workbook attached to that post
Last edited by nigelog; 10-11-2018 at 12:12 PM.
Hi Pete,
Thank you but having the functionality of a macro dropdown in the middle of the page will enhance user interface experience.
Thank you
Attached is an example of what i want assuming all the choices were shown on the first tab.
Ideally a dropdown would look better
example.pdf
Just use data validation list for dropdown. It's not like you need it to be dynamic (month name doesn't increase from 12...).
how do i apply this on the workbook ? ive included it in a module and added a data validation on A2 to try it out but the macro doesnt appear in the macro list ?Originally Posted by CK76
You paste in the macro to Sheet module.
In your case, go into VBE, double click on your Balance sheet in Project explorer pane, and paste in the code.
See sample attached. Change dropdown selection in A2 of "Balance" sheet, and it will immediately jump to cell A1 of selected sheet name.
Here is a (sort of) non-VBA approach, it uses a User Defined Function. This option does not use a DD, but instead, lists all sheet names, which are clickable to take you to the sheet you want.
1st create a range name (I called mine Sheetnames)
Then put this in the Refers To box: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")
Seeing as how you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")
Note that this uses a UDF (User Defined Function) and requires that you save the file a .xlsm
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks