Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resizing Toolbar Controls Automatically
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).ListInde x = i Exit Sub End If Next i 'IF THERE'S NO WORKSHEET SELECTED, LEAVE DD BLANK CommandBars(TBar_Name).Controls(DD1_Name).ListInde x = 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).ListInde x = i Exit Sub End If Next i 'IF THERE'S NO CHART SELECTED, LEAVE DD BLANK CommandBars(TBar_Name).Controls(DD2_Name).ListInde x = 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:=msoContr olDropdown) 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:=msoContr olDropdown) 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resizing Toolbar Controls Automatically | Excel Programming | |||
Resizing Toolbar Controls Automatically | Excel Programming | |||
Control Toolbox Controls keep resizing ?? | Excel Programming | |||
Controls auto resizing with scree resolution | Excel Programming | |||
resizing and aligning groups of ActiveX controls together | Excel Programming |