Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop down to open a different spreadsheet?
Okay, so I've got this workbook. It's a fairly complex budget workbook, and
one (well, twelve, actually) of the sheets are "daily spending," and have every day in the month outlining what was bought each day, for how much, etc. My question is: is it possible to put in a drop-down list that will allow the different month's daily spending spreadsheet to show on the current page? I want the drop-down to have the options for "Current Month," and January-December. I'm sorry if I'm explaining this badly... So, I want one worksheet to be visible and it will always have one of the months open, but if you choose another month, it will switch to that month and display that one. How can I do this? Thank you SO much for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop down to open a different spreadsheet?
You would have to employ a userform or similar or have a drop-down on every
sheet. May be just as easy to right-click on the navigation arrows at bottom left and choose the sheet from the list. If you really want code here is a macro from Bob Phillips that pops up a list of sheets to choose from. Sub BrowseSheets() Const nPerColumn As Long = 38 'number of items per column Const nWidth As Long = 13 'width of each letter Const nHeight As Long = 18 'height of each row Const sID As String = "___SheetGoto" 'name of dialog sheet Const kCaption As String = " Select sheet to goto" 'dialog caption Dim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim cLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As OptionButton Application.ScreenUpdating = False If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.DialogSheets(sID).Delete Application.DisplayAlerts = True On Error GoTo 0 Set CurrentSheet = ActiveSheet Set thisDlg = ActiveWorkbook.DialogSheets.Add With thisDlg .Name = sID .Visible = xlSheetHidden 'sets variables for positioning on dialog iBooks = 0 cCols = 0 cMaxLetters = 0 cLeft = 78 TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count If i Mod nPerColumn = 1 Then cCols = cCols + 1 TopPos = 40 cLeft = cLeft + (cMaxLetters * nWidth) cMaxLetters = 0 End If Set CurrentSheet = ActiveWorkbook.Worksheets(i) cLetters = Len(CurrentSheet.Name) If cLetters cMaxLetters Then cMaxLetters = cLetters End If iBooks = iBooks + 1 .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5 .OptionButtons(iBooks).text = _ ActiveWorkbook.Worksheets(iBooks).Name TopPos = TopPos + 13 Next i .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24 CurrentSheet.Activate With .DialogFrame .Height = Application.Max(68, _ Application.Min(iBooks, nPerColumn) * nHeight + 10) .Width = cLeft + (cMaxLetters * nWidth) + 24 .Caption = kCaption End With .Buttons("Button 2").BringToFront .Buttons("Button 3").BringToFront Application.ScreenUpdating = True If .Show Then For Each cb In thisDlg.OptionButtons If cb.Value = xlOn Then ActiveWorkbook.Worksheets(cb.Caption).Select Exit For End If Next cb Else MsgBox "Nothing selected" End If Application.DisplayAlerts = False .Delete End With End Sub Gord Dibben MS Excel MVP On Wed, 28 Feb 2007 13:44:00 -0800, Lauren Giles wrote: Okay, so I've got this workbook. It's a fairly complex budget workbook, and one (well, twelve, actually) of the sheets are "daily spending," and have every day in the month outlining what was bought each day, for how much, etc. My question is: is it possible to put in a drop-down list that will allow the different month's daily spending spreadsheet to show on the current page? I want the drop-down to have the options for "Current Month," and January-December. I'm sorry if I'm explaining this badly... So, I want one worksheet to be visible and it will always have one of the months open, but if you choose another month, it will switch to that month and display that one. How can I do this? Thank you SO much for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down box resizing on open | New Users to Excel | |||
Format drop down option won't open on selected cells | Excel Discussion (Misc queries) | |||
Theres no toolbars, no drop-down menus when I open Excel | Setting up and Configuration of Excel | |||
Open hidden sheets from a drop down list selection | Excel Discussion (Misc queries) | |||
drop down box to open when selected | Excel Worksheet Functions |