Resizing Toolbar Controls Automatically

[shoeless]

To Whome It May Concern:

At times I work with workbooks with numerous worksheets and chartsheets

(15+) and it becomes cumbersome to navigate through the workbook. I
have tried using the short-cut key strokes to navigate but this is time

consuming cycling back and forth, so I thought it would be nice to
create a custom toolbar to navigate such workbooks. I decided that two

drop down boxes would do nicely, one for navigating worksheets and the
other for chartsheets. I also thought about adding to arrow buttons,
"previous" and "next", to cycle through the workbook as well...?


Anyway the problem I ran into is that some of the worksheets and
chartsheets names are two large to fit in the drop down boxes (default
size); although you can manualy resize the drop down boxes by going to
tools --> customize and then resize any toolbar command-control, I
wanted to be able to have the drop down boxes resize automatically
based on the largest name of the worksheets' and chartsheets'.


Does anyone know if it is possible to program a macro to automatically
resize a toolbar command-control based on the largest name of a
worksheet/chartsheet?
And if so, how? It would be nice to have this feature so that I can
publish it as an add-in and not have to worry about adjusting it all
the time and having to deal with copying the code to other workbooks...



I originally wanted to make a verticle list of buttons (with the names
of the worksheets and chartsheets as captions) along the left-hand side

of the screen, but I could not figure out how to do it. Does any one
know if this is even possible? And if so, any words of wisdom?


Also if anyone has any suggestions or comments on what I am trying to
do I am open ears.
Much thanks to those who reply; cheers,
-->Brian


If anyone is interested this is what I have done so far:


---------------------BEGINNING OF MACRO-------------------------------


'PUBLIC DECLARATIONS
Public TBar_Name
Public DD1_Name
Public DD2_Name


Private Sub worksheet_SelectionChange()
'AUTOMATICALLY CHANGE DROP DOWN BOX TEXT TO CURRENT/ACTIVE WORKSHEET


For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveSheet.name = Worksheets(i).name Then
CommandBars(TBar_Name).Controls(DD1_Name).ListIndex = i
Exit Sub
End If
Next i


'IF THERE'S NO WORKSHEET SELECTED, LEAVE DD BLANK
CommandBars(TBar_Name).Controls(DD1_Name).ListIndex = 0


End Sub


Private Sub Chartsheet_SelectionChange()
'AUTOMATICALLY CHANGE DROP DOWN BOX TEXT TO CURRENT/ACTIVE CHARTSHEET


For i = 1 To ActiveWorkbook.Charts.Count
If ActiveChart.name = Charts(i).name Then
CommandBars(TBar_Name).Controls(DD2_Name).ListIndex = i
Exit Sub
End If
Next i


'IF THERE'S NO CHART SELECTED, LEAVE DD BLANK
CommandBars(TBar_Name).Controls(DD2_Name).ListIndex = 0


End Sub


Sub Create_Toolbar()


Dim TBar As CommandBar
Dim NewDD As CommandBarControl


'VARIABLE DECLARATIONS
TBar_Name = "Workbook Navigator"
DD1_Name = "Worksheets"
DD2_Name = "Chartsheets"


'DELETE EXISTING TOOLBAR IF IT EXISTS
On Error Resume Next
CommandBars(TBar_Name).Delete
On Error GoTo 0


'CREATE NEW TOOLBAR
Set TBar = CommandBars.Add
With TBar
.name = TBar_Name
.Visible = True
End With


'ADD A DROPDOWN CONTROL FOR WORKSHEETS
Set NewDD =
CommandBars(TBar_Name).Controls.Add(Type:=msoControlDropdown)
With NewDD
.Caption = DD1_Name
.OnAction = "Activate_Selected_Worksheet"
.Style = msoButtonAutomatic


'FILL THE DROP DOWN WITH THE NAMES OF THE WORKSHEETS WITHIN THE
WORKBOOK
For i = 1 To ActiveWorkbook.Worksheets.Count
.AddItem Worksheets(i).name
Next i
.ListIndex = 1
End With


'ADD DROPDOWN CONTROL FOR CHARTSHEETS
Set NewDD =
CommandBars(TBar_Name).Controls.Add(Type:=msoControlDropdown)
With NewDD
.Caption = DD2_Name
.OnAction = "Activate_Selected_Chart"
.Style = msoButtonAutomatic


'FILL THE DROP DOWN WITH THE NAMES OF THE CHARTSHEETS WITHIN THE
WORKBOOK
For i = 1 To ActiveWorkbook.Charts.Count
.AddItem Charts(i).name
Next i
.ListIndex = 1
End With


End Sub


Sub Activate_Selected_Worksheet()


'ACTIVATE WORKSHEET WHEN IT IS CHOSEN FROM THE DROP DOWN BOX
With CommandBars(TBar_Name).Controls(DD1_Name)
Worksheets(.List(.ListIndex)).Activate
End With


End Sub


Sub Activate_Selected_Chart()


'ACTIVATE CHARTSHEET WHEN IT IS CHOSEN FROM THE DROP DOWN BOX
With CommandBars(TBar_Name).Controls(DD2_Name)
Charts(.List(.ListIndex)).Activate
End With


End Sub