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
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