Text Box Link to sheet in another spreadsheet

Hanz77

Board Regular
Joined
Sep 21, 2006
Messages
87
I have a text box that is currently set up to open another spreadsheet. I would like to set it up to open up to a certain sheet within the second spreadsheet. The problem is that the sheet name will change. Following is an example of what I am trying to accomplish.

I have a spreadsheet called Book1. This spreadsheet contains a summary of activity for each month (Jan, Feb, Mar, etc.). It is set up so that the user can simply enter the month they want to view by typing Jan, Feb, Mar, etc. in cell A1. Based on the month entered in to cell A1, a series of formulas populates a summary of data. The detailed information is stored in another spreadsheet called Book2 and this spreadsheet contains 12 sheets (Jan through Dec). I currently have a text box set up in Book1 that allows the user to click on the box and it takes them directly to Book2. I would like to tweak this a little so that when the user clicks on the text box in Book1 it opens to the corresponding month entered in cell A1 with the sheet in Book2. Hope this makes sense. Essentially if a user types Mar in cell A1 of Book1, clicking on the text box will open to sheet Mar in Book2.

Any thoughts or ideas on accomplishing this is appreciated.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
I'd use something like this.
Code:
Dim sheetName as string
sheetName=Workbooks("book1").Worksheets("sheet1").Value
Workbooks("book2").Worksheets(sheetName).Activate
 

Hanz77

Board Regular
Joined
Sep 21, 2006
Messages
87
I'm struggling to make this code work. I tried modifying this so that it looks at cell A1 in Book1 for the month so that it knows wich sheet or tab to look at when it opens Book2. So far I have not been able to get this to work. Any suggestions are appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,102
Messages
5,640,115
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top