Goto link macro
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Goto link macro

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm fairly new to macro writing but I have written a few. I wrote a macro today whose purpose is to follow the links between sheets in a workbook and then take you back to where you started. (I couldn't find any other quick way of doing this in excel). It's not idiot proof nor can you go to more than 3 links without dropping the "trail back to the start".

    I was just curious as to whether anyone had a better more idiot proof way of doing this.
    Any other pointers would be appreciated as well.

    My macro is below:
    Option Explicit
    Dim StartSheet
    Dim SecondSheet
    Dim ThirdSheet



    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 3/14/2002 by Gary P. Turenne
    '

    '
    Dim CurrLoc
    Dim CurrentFormula
    Dim Length
    Dim Result
    On Error GoTo ErrorHandle
    If StartSheet = Empty Then
    StartSheet = ActiveSheet.Name
    End If
    CurrentFormula = ActiveCell.Formula
    Length = Len(CurrentFormula)
    CurrLoc = Right(CurrentFormula, Length - 2)
    Application.Goto Range(CurrLoc), False
    ActiveSheet.Buttons.Add(144, 0, 48, 12.75).Select
    Selection.OnAction = "DeleteButton"
    Selection.Characters.Text = "Return"
    Range(CurrLoc).Select
    If StartSheet = Empty Then
    StartSheet = ActiveSheet.Name
    ElseIf SecondSheet = Empty Then
    SecondSheet = ActiveSheet.Name
    ElseIf ThirdSheet = Empty Then
    ThirdSheet = ActiveSheet.Name
    End If

    Exit Sub
    ErrorHandle:

    End Sub

    Sub DeleteButton()
    On Error GoTo ErrorHandle
    ActiveSheet.Shapes(1).Select
    Selection.Cut
    If ActiveSheet.Name = StartSheet Then
    Sheets(StartSheet).Select
    StartSheet = Empty
    ElseIf ActiveSheet.Name = SecondSheet Then
    Sheets(StartSheet).Select
    SecondSheet = Empty
    StartSheet = Empty
    ElseIf ActiveSheet.Name = ThirdSheet Then
    Sheets(SecondSheet).Select
    ThirdSheet = Empty
    End If


    Exit Sub
    ErrorHandle:


    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi --

    If the script works be proud you wrote it and i would stick with my codes and develope then bit by bit.

    Good stuff, well done!

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com