Adding Basic shape left/right arrow as a clickable previous/next button

Question

I am looking at writing a little bit of VBA code that will change displayed worksheet to the previous or next sheet in the order of the tabs.

I found this stackoverflow question which talks about adding an arrow, but its more like a leader arrow (red in the picture).

I want to add the LEFT or RIGHT arrow from the Insert ribbon -> illustrations section -> Shapes drop down:

The snipit of code I am using is:

Dim l As Long
Dim t As Long

l = Range("O3").Left
t = Range("Q3").Top

Item_Estimate_Sheet.Shapes.AddConnector(msoConnectorStraight, t + 89.25, l + 89.25, l, t).Select

With Selection
    With .ShapeRange.Line
        .EndArrowheadStyle = msoArrowheadOpen
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Weight = 1.5
    End With
End With

What I am hoping to add is something that looks like the following which I added manually:

Part B

Avoiding use of select. I hear on here time and time again that select is to be avoided and its not required. I tried removing select and when I did this, it caused an error. A .something seems to be required at the the end of the add shape line. Is there a better way to do this?

Part C: Is there a way to get the dimensions/ properties of the shape you inserted other than recording a macro at the time of insertion? (and damn why did I not remember to record a macro)

UPDATE:

so I recorded a macro and edited my code as follow:

Item_Estimate_Sheet.Shapes.AddShape(msoShapeRightArrow, 859.5, 35.25, 25.5, 19.5).Select

With Selection
    With .ShapeRange.Line
        .Name = "NEXT"
        .Top = Range("S3").Top
        .Left = Range("S3").Left
        .Width = Range("Q3").Width * 2
        .Height = Range("Q3").Height * 2
    End With
End With

when I got rid of the select as follows it threw an error..something about not acceptable method:

With Item_Estimate_Sheet.Shapes.AddShape(msoShapeRightArrow, 859.5, 35.25, 25.5, 19.5)

    With .ShapeRange.Line
        .Name = "NEXT"
        .Top = Range("S3").Top
        .Left = Range("S3").Left
        .Width = Range("Q3").Width * 2
        .Height = Range("Q3").Height * 2
    End With
End With

So are you looking to insert the `msoShapeRightArrow` and `msoShapeLeftArrow` on each worksheet in your workbook through VBA on the same position with predefined measurements and assign a macro to them that will activate the sheet before/after? — JvdV, Sep 10 '19 at 20:54
You can avoid the use of `Selection` by capturing the result of `Shapes.AddConnector` (and removing `.Select`) into a local object variable, or heck the `With` block could even own it: `With Item_Estimate_Sheet.Shapes.AddConnector(...)` — Mathieu Guindon, Sep 10 '19 at 20:58
More or less yes. I have some code that will create a worksheet for every item selected in a list. As that sheet gets created I was going to add the arrow shape. — Forward Ed, Sep 10 '19 at 20:58
@MathieuGuindon I thought I had tried it with the with and got an error...been doing a lot of editing so I will have to go back and double check that iteration and see if it was nothing else causing issues. — Forward Ed, Sep 10 '19 at 21:00
The correct syntax (I think) would be to set a shape object which you can then access it's properties like : `Set shp = Item_Estimate_Sheet.Shapes.AddShape(msoShapeRightArrow, 859.5, 35.25, 25.5, 19.5)`. — JvdV, Sep 10 '19 at 21:19

JvdV · Accepted Answer · 2019-09-10T21:48:05.423

4

I'll put this up, but I'm quite unsure what exactly your aim is with the .Line property of the .ShapeRange object. So my take on this is to just insert some arrows, assign them a .Name and .OnAction while further on I grouped them to assign more common properties like .Fill.

Obviously it's a draft but you might be able to get some understanding how (I think) it's done.

Sub InsertArrows()

Dim rngL As Range, rngR As Range
Dim shpL As Shape, shpR As Shape

For Each sht In ThisWorkbook.Sheets
    Set rngL = sht.Range("B2")
    Set rngR = sht.Range("C2")

    Set shpL = sht.Shapes.AddShape(msoShapeLeftArrow, rngL.Left, rngL.Top, rngL.Width, rngL.Height)
    With shpL
        .Name = "Last"
        .OnAction = "LastSheet"
    End With

    Set shpR = sht.Shapes.AddShape(msoShapeRightArrow, rngR.Left, rngR.Top, rngR.Width, rngR.Height)
    With shpR
        .Name = "Next"
        .OnAction = "NextSheet"
    End With

    Set shpRng = sht.Shapes.Range(Array("Last", "Next"))
    With shpRng
        .Fill.ForeColor.RGB = RGB(255, 0, 0)
        .Line.ForeColor.RGB = RGB(255, 0, 0)
        'More common properties.....
    End With
Next sht

End Sub

Sub LastSheet()
    'Some code to get to the last sheet 
End Sub

Sub NextSheet()
    'Some code to get to the next sheet 
End Sub

edited Sep 10 '19 at 21:48

answered Sep 10 '19 at 21:42

JvdV

70,606
8
39
70

JvdV you have the gist of what I am after. I was chunking my code together from different sources. What you are showing here reads a heck of a lot nicer and also has the next step for me built of linking the shape to some code in a sub. I'll try it out when I am back at work tomorrow and have some free time. – Forward Ed Sep 10 '19 at 23:59
++ Nice one :) I do this slightly [differently](https://prnt.sc/p4j3sr) :D – Siddharth Rout Sep 11 '19 at 03:10
@SiddharthRout Nice screen shot...are those graphics built into Excel or are they a custom graphic? We were discussing how a graphic can be much easier/quicker to interpret than a bunch of buttons with text on them that need reading. – Forward Ed Sep 11 '19 at 13:30
@ForwardEd Custome Graphics downloaded from web and the entire "menu shape" is stored in a settings sheet. So basically, no need to create it again and again. Just copy it when the new worksheet is created. – Siddharth Rout Sep 11 '19 at 13:31
1

I would go with `"Previous"` instead of `"Last"`, but otherwise, I concur. Nice answer – FreeMan Sep 11 '19 at 13:50
@SiddharthRout I just want to toss your description back in my own words to make sure I have the understanding correct. The graphic(s) (3 separate buttons or 1 big button?) are placed in a worksheet that is stored in the file and probably hidden. When the buttons need to be added to a worksheet, they are simply referenced from that storage sheet using some modified code that does something similar to add shape? – Forward Ed Sep 11 '19 at 14:01
@ForwardEd: The 3 buttons and the rectangle are a group. They already are connected to the relevant macros. They are placed in a hidden sheet. When a new sheet is added, I simply copy the shape(group) and paste it in the new sheet. As simple as that. Let me know if you want me to post an answer. (Obviously it doesn't answer your question but can be an alternative) – Siddharth Rout Sep 11 '19 at 14:14
@SiddharthRout would love to see it as an alternative if you don't mind. Even if I do not use it directly, I am sure I will learn a new technique or two. May even be helpful to some futr user that stumbles on this Q. – Forward Ed Sep 11 '19 at 14:18
@SiddharthRout No rush. I just had a stack of CAD drawings dumped on my desk so I will be busy with actual work related stuff for a good chunk of the dat now. Wont get back to tinkering with coding for 4 hours or more. – Forward Ed Sep 11 '19 at 14:22
1

@JvdV Because I am inserting the Navigation arrows as the sheet is created, I am dropping the for each loop. Thank you for including that as FOR EACH for whatever reason tends to be a stumbling block for me and I avoid using it. I was not familiar with the VBA Array("Last","Next") So I definitely learned something new there and I can see this being useful on some other things I have done. Especially for initializing an array it a known setup. I have decided to leave your code a a standalone sub which I will call from the INSEert SHEET routine and I will just pass it the SHT and ref cells. – Forward Ed Sep 11 '19 at 14:35
@ForwardEd, that seems to me like a very nice way to implement things! Well done :) – JvdV Sep 11 '19 at 14:53

score 4 · Answer 2 · answered Sep 11 '19 at 14:48

Note: As mentioned in the comments this is not an answer but on OPs request, I am posting this alternative.

For demonstration purpose I have two sheets in a workbook. One is called HOME and the other is called SETTINGS. Link to Sample File is given at the end of the post

I have downloaded 3 shapes from the web. Feel free to choose any other images.

Paste this code in a module

Option Explicit

Dim thisSheetCount As Long
Dim nextSheetCount As Long
Dim totSheetCount As Long

Sub InsertNewSheet()
    Dim ws As Worksheet
    Dim wsSettings As Worksheet
    Dim shpOrig As Shape, shpN As Shape

    Set wsSettings = Sheet1
    Set shpOrig = wsSettings.Shapes("MyMenu")

    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    shpOrig.Copy
    ws.Paste

    Set shpN = ws.Shapes(1)
    With shpN
        .Top = shpOrig.Top
        .Left = shpOrig.Left
    End With
    DoEvents
    [A1].Activate
End Sub

Sub NextSheet()
    thisSheetCount = ActiveSheet.Index
    totSheetCount = ThisWorkbook.Sheets.Count

    nextSheetCount = thisSheetCount + 1

    If nextSheetCount > totSheetCount Then
        MsgBox "No more sheets"
        Exit Sub
    End If

    Do While nextSheetCount <= totSheetCount
        If ThisWorkbook.Sheets(nextSheetCount).Visible = False Then
            nextSheetCount = nextSheetCount + 1
        Else
            ThisWorkbook.Sheets(nextSheetCount).Activate
            Exit Do
        End If

        If nextSheetCount > totSheetCount Then
            MsgBox "No more visible sheets found"
        End If
    Loop
End Sub

Sub PrevSheet()
    thisSheetCount = ActiveSheet.Index
    nextSheetCount = thisSheetCount - 1

    If nextSheetCount < 1 Then
        MsgBox "No more sheets"
        Exit Sub
    End If

    Do While nextSheetCount >= 1
        If ThisWorkbook.Sheets(nextSheetCount).Visible = False Then
            nextSheetCount = nextSheetCount - 1
        Else
            ThisWorkbook.Sheets(nextSheetCount).Activate
            Exit Do
        End If

        If nextSheetCount < 1 Then
            MsgBox "No more visible sheets found"
        End If
    Loop
End Sub

Sub HomeSheet()
    ThisWorkbook.Sheets("Home").Visible = True
    ThisWorkbook.Sheets("Home").Activate
End Sub

Next I insert a rectangle shape in SETTINGS sheet. Then I insert the 3 shapes and resize them as shown below

Right click on the previous button and click on Assign Macro. Connect it to Sub PrevSheet(). Similarly, connect next button to Sub NextSheet() and finally connect home button to Sub HomeSheet()

Next, group the Shapes. Call them MyMenu or whatever you want. I will use MyMenu for demonstration purpose.

Next Copy the shape and paste it in the HOME Sheet. Ungroup the shape and delete the previous and the home button. HOME sheet is supposed to be the left most sheet and should always remain visible.

Insert a button in the HOME sheet and let's call it "Insert New Sheet". Assign Sub InsertNewSheet() to this button.

Hide the SETTINGS Sheet and we are done.

To test it simply click on the Insert New Sheet button.

One thing that I would like to point out regarding the Next and the Previous button code is that, it has been tweaked to work only for visible workssheets. For example: Insert 4 worksheets and hide the 3rd one. Go to the 2nd sheet and click on next button. See what happens. Also check what happens when you click on the next button in the last worksheet.

Screenshot in action

Sample File can be downloaded from HERE

Thank you for the detailed explanation. Its a nice easy read to follow. I have not had a chance to play with it yet. I have not looked into the previous/next routines yet as I was thinking I would need to make some adjustments as I was guessing +1/-1 to the index would not work in my case. I had not considered hidden sheets so thanks for putting that on my radar. In my case sheet can be added a any time, but they get sorted alphabetically. Meaning the 17th sheet to be created may wind up being the 3rd sheet in the tabs list. — Forward Ed, Sep 11 '19 at 16:07
I was assuming the worksheet index was the same as list on the left when you are in the VBA editor. oh and to top it off, I have an exception list for the sort routine so about 3-4 sheets are not included in the sort and are locked as first few sheets and last few sheets. Fun stuff to wrap my head around when I have time to sit down and think about it. — Forward Ed, Sep 11 '19 at 16:09
Good news...apparently my understanding of sheet index number and the way they are displayed in the project menu are completely different. Life just become easier! Again thanks for showing me this approach. — Forward Ed, Sep 11 '19 at 18:41
What is the purpose of the DoEvents line after adjusting the position of the shape? — Forward Ed, Sep 14 '19 at 01:56
Just giving Excel time. Usually it would not be required here but becuase the `.Activate` I wanted Excel to finish whatever it is doing. — Siddharth Rout, Sep 14 '19 at 05:02

Adding Basic shape left/right arrow as a clickable previous/next button

2 Answers2