Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to switch sheets
Hi everyone:
I heed some help to create a form that I can use to switch sheets. The reason is I have a workbook that has so many sheets I can't always see them all. I do have some VBA experience and will be abe to edit. Thanks for your help Suzie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to switch sheets
Suzie,
A simple possibility is Userform with a combobox containing sheet names. HTH Private Sub ComboBox1_Change() Worksheets(ComboBox1.Value).Activate End Sub Private Sub UserForm_Initialize() For Each Wks In ThisWorkbook.Worksheets ComboBox1.AddItem Wks.Name Next End Sub "suzie" wrote: Hi everyone: I heed some help to create a form that I can use to switch sheets. The reason is I have a workbook that has so many sheets I can't always see them all. I do have some VBA experience and will be abe to edit. Thanks for your help Suzie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I create a macro to switch sheets
Suzie
This from 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 Gord Dibben MS Excel MVP On Thu, 19 Jan 2006 11:35:04 -0800, "suzie" wrote: Hi everyone: I heed some help to create a form that I can use to switch sheets. The reason is I have a workbook that has so many sheets I can't always see them all. I do have some VBA experience and will be abe to edit. Thanks for your help Suzie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switch between sheets with keystrokes? | Excel Worksheet Functions | |||
Macro to create new workbook and sheets | Excel Discussion (Misc queries) | |||
Cannot switch between sheets... | Excel Discussion (Misc queries) | |||
Use a macro to create new sheets | Excel Programming | |||
switch between sheets | Excel Discussion (Misc queries) |