Creating MenuRow for Sheets using an Excel VBA

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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: