Selva’s Site

Contributed by Selva V Pasupathy





The following code illustrates the way you can make the menubar on the first row of all the sheets. Just copy the code in module code area and run the macro, it will insert a new row at the top of all the sheets and create links for each sheet.





Sub CreateMenuRow_Sheets()
Dim i, shtNm As Integer
Dim myLeft As Long
Const Mytop = 0
Const myWidth = 75
Const myHeight = 14

For shtNm = 1 To ActiveWorkbook.Sheets.Count
    ActiveWorkbook.Sheets(shtNm).Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.RowHeight = myHeight

    For i = 1 To Sheets.Count
        myLeft = 100 + (i - 1) * myWidth
        ActiveSheet.Shapes.AddShape(msoShapeFlowchartProcess, myLeft, Mytop, _
                                    myWidth, myHeight).Select
        Selection.Characters.Text = Sheets(i).Name

        With Selection.Characters.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 8
            .Underline = xlUnderlineStyleSingle
            .ColorIndex = 2
        End With
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
            .Orientation = xlHorizontal
            .AutoSize = False
        End With
        Selection.ShapeRange.Fill.Visible = msoTrue
        Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
        Selection.ShapeRange.Fill.Transparency = 0.3
        Selection.ShapeRange.Line.Weight = 1#
        Selection.ShapeRange.Line.Visible = msoTrue
        Selection.ShapeRange.Line.ForeColor.RGB = RGB(230, 0, 0)
        With Selection
            .Placement = xlFreeFloating
            .PrintObject = True
        End With
        ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:= _
            "", SubAddress:="'" & Sheets(i).Name & "'!A1"
    Next i
Next shtNm
End Sub

To Download example Workbook CLICK HERE