I'm currently using the following to generate a list of all the worksheet names in my workbook 
Please Login or Register  to view this content.

I then use this list to populate a dropdown in my ribbon using Jim Rech's dynamic dropdown code This sub currently runs every time Sheet1 is selected.

The selection the user then makes is used to drive all of the formulas in my charts and such.

If possible, I would like to...
1. Skip the writing of the sheet names to a range and have it directly populate in the dropdown
2. Have the user selection from the dropdown write to cell A1 of Sheet1 (I've had trouble making Jim's code write the returned value to my worksheet)