Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
open worksheet with a macro (combobox)
Hi, well as usual simple things you can't find in the user manual I was looking forward to create a simple macro using a combobox to select one of 100 worksheet in a workbook. How can i open a worksheet is there any kind of command for doing so, how can i put all worksheets into the combobox without programming, or changing the code everytime (user creates a new worksheet for example) Thank you very much Matthias -- matthiasmorath ------------------------------------------------------------------------ matthiasmorath's Profile: http://www.excelforum.com/member.php...o&userid=34970 View this thread: http://www.excelforum.com/showthread...hreadid=547071 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
open worksheet with a macro (combobox)
Hi Matthias,
Try assigning the following macro, posted by Jim Rech, to a toolbar button: '================== Sub ShowSheetList() 'Jim Rech On Error Resume Next If ActiveWorkbook.sheets.Count <= 16 Then Application.CommandBars("Workbook Tabs"). _ ShowPopup 500, 225 Else Application.CommandBars("Workbook Tabs"). _ Controls("More Sheets...").Execute End If On Error GoTo 0 End Sub '<<================== For an alternative approach, try a suugestion by Bob Phillips: '============= 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 '<<============= --- Regards, Norman "matthiasmorath" <matthiasmorath.28oahy_1149080402.4247@excelforu m-nospam.com wrote in message news:matthiasmorath.28oahy_1149080402.4247@excelfo rum-nospam.com... Hi, well as usual simple things you can't find in the user manual I was looking forward to create a simple macro using a combobox to select one of 100 worksheet in a workbook. How can i open a worksheet is there any kind of command for doing so, how can i put all worksheets into the combobox without programming, or changing the code everytime (user creates a new worksheet for example) Thank you very much Matthias -- matthiasmorath ------------------------------------------------------------------------ matthiasmorath's Profile: http://www.excelforum.com/member.php...o&userid=34970 View this thread: http://www.excelforum.com/showthread...hreadid=547071 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to remove macro as I open worksheet? | Excel Discussion (Misc queries) | |||
Auto run macro on individual worksheet open | Excel Programming | |||
Help with a macro to open to a specific worksheet | Excel Worksheet Functions | |||
How do I get my personal macro worksheet to open whenever I open . | Excel Discussion (Misc queries) | |||
Open Worksheet Macro | Excel Programming |