Text Box Link to sheet in another spreadsheet

[Hanz77]

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.

 

[mikerickson]

I'd use something like this.

Dim sheetName as string
sheetName=Workbooks("book1").Worksheets("sheet1").Value
Workbooks("book2").Worksheets(sheetName).Activate

 

[Hanz77]

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.