On 29 Sep, 11:40, "Jim Rech" <n...@abc.com> wrote:
> After:
> ActiveWorkbook.Worksheets(cb.Caption).Select
>
> Add:
> Range("A1").Select
>
> "Johnnyboy5" <intermediatec...@gmail.com> wrote in message
>
> news:4c31cb5e-1b14-40aa-a1cd-(E-Mail Removed)...
>
>
>
> > Credit to "Bob Phillips" for the macro below, *which will give you a
> > pick list of sheets you want to goto on opening the workbook.
>
> > What I need is someone to show me how to get it to go to Cell A1 after
> > selecting the desired sheet.
>
> > Thanks
>
> > Johnnyboy
>
> > Sub Auto_open()
>
> > 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- Hide quoted text -
>
> - Show quoted text -
Great - works just fine.
Thanks for speedy help.
John
|
Similar Threads
