Create Generic Hyperlink to Move to "First", "Previous", "Next" and "Last" Sheet within the same Workbook using Left & Right Autoshapes.

[all4excel]

Create Generic Hyperlink to Move to "First", "Previous", "Next" and "Last" Sheet within the same Workbook using Left & Right Autoshapes.

Dear Board,

I have created Four Autoshapes using Left and Right Autoshapes and I need to use these Autoshapes to go to different Sheets..

Left Autoshape I - "First" - Needs to take me to the very First Sheet in the Workbook.

Left Autoshape II - "Previous" - Needs to take me to the Previous Sheet before the ActiveSheet in the Workbook.

Right Autoshape I - "Next" - Needs to take me to the Next Sheet in the Workbook.

Left Autoshape II - "Previous" - Needs to take me to the Last Sheet in the Workbook.

I have used the following link to create these Autoshapes..http://www.mrexcel.com/forum/excel-questions/305014-rename-autoshape-adding-text.html

Sub AddShapes()
    Dim ShapeObj As Object
    Dim ShpName As String
    Dim MacroName As String
    
    MacroName = "First"
    '-------------------------------------------------------------------------
    ActiveSheet.Range("A1").Activate ' only works if the sheet is the Active one
    ShpName = "First"
    '-------------------------------------------------------------------------
    '(Across Page, Down Page, length, width)
    Set ShapeObj = _
        ActiveSheet.Shapes.AddShape(msoShapeLeftArrow, Range("E1").Left + 2, Range("E1").Top + 2, Range("E1").Width - 4, 38)
    '--------------------------------------------------------------------------
    With ShapeObj
        '----------------------------------------------------------------------
        .Fill.ForeColor.RGB = RGB(0, 0, 255)
        .Fill.BackColor.RGB = RGB(0, 0, 97)
        .Fill.TwoColorGradient msoGradientHorizontal, 1
        '---------------------------------------------------------------------
        '- NAME THE RECTANGLE
        .Name = ShpName
        '---------------------------------------------------------------------
        '- RUN A MACRO
        '  .OnAction = MacroName
        '---------------------------------------------------------------------
        With .TextFrame
            .Characters.Text = ShpName
            .Characters.Font.Size = 14
            .Characters.Font.Name = "Times New Roman"
            .Characters.Font.Bold = True
            .Characters.Font.ColorIndex = 2
            .HorizontalAlignment = xlHAlignCenter
            .VerticalAlignment = xlVAlignCenter
        End With
        '--------------------------------------------------------------------
    End With

End Sub

Can someone also guide me hot o have these 4 Autoshapes one below the other in a loop with a small gap of just 2 in between any two Autoshapes as well as the Hyperlinks..

Warm Regards
all4excel

 

[Pup Denab]

Hi Allexcel
You can do this, very quickly and easily by using clip art, pick your arrow, then right click on it add hyper link,
sheet 1 arrow 1 = next page, sheet 2 hyper link
arrow 2 = sheet 10 or last sheet hyper link

sheet 2 arrow 1 = back page sheet 1 hyper link
arrow 2 = next page sheet 3 hyper link
arrow 3 = sheet 10 last sheet hyper link
ect

or you could just list the page number and add hyper links to each art number, meaning that the user could go to any page with only 1 click
Regards
Pup

I'm sure you can do this in vba, but this way is very fast & easy to do

 

[all4excel]

Thanks Pup I know I can do it simply however I want to do it with VBA as the Sheets where these Left and Right Arrow Autoshapes are to be used are created by VBA...

Now after posting the query i found a solution to this too using VBA however I face another difficulty which i had not realised which is I am making copies of SHeets and then moving it to another workbook with VBA and then I delete the code from the WOrkbook so I basically strip the Copy_pasted workbook of any VBA Module code...but how do i make this work if there's no VBA code..if I still need this to be generated by VBA ...

 

[all4excel]

I used this code to Move along in the WorkBook ..

Sub First()


ActiveWorkbook.Worksheets(1).Activate


End Sub


Sub Previous()


ActiveSheet.Previous.Select


End Sub


Sub NextSht()


Sheets(ActiveSheet.Index + 1).Activate


End Sub


Sub Last()


Worksheets(Worksheets.Count).Activate


End Sub

Now these Sheets when Copied and Pasted in the other WorkBook dont have this VBA code so though it has the Autoshapes it does not work...


So whats the workaround..

And I cant do it manually as I ahve around three similar files and three other files with different placements for the autoshapes which I am still working on..


Ex:
This is the way my SHeets would be placed in the Distributed File..

\Acknowledegment\P-1\L-1\P-2\L-2\P-3\L-3

Now uptill now I have added the Autoshapes in the SHeets P-1 and P-2 and P-3 with VBA... which does the work of moving within the workbook however if fails to do so in the copy-pasted workbook as i have to remove the vba code from it..

SO how do i keep only this code..?

Regards
all4excel