CODEWITHSUNDEEP
vbaexcel
dhruva_04
dhruva_04

Reputation: 141

How to refer to shapes in a module

I have created different shapes in excel and have assigned a macro to it which functions as activating another sheet. I want to put all these under one macro and then assign it to different shapes with different linking property. But this code doesn't work because obviously I am doing something stupid. Can someone please help?

Dim shp As ShapeRange, ws As Sheets, i As Integer
Set ws = ActiveWorkbook.Sheets(Array("Introduction", "S1 Fuel Consumption", "S1 Fugitive", "S2 Electricity Consumption"))
Set shp = ws(2).Shapes.Range(Array("Chevron1", "Chevron2"))

Select Case shp(i)

Case shp(1)
ws(1).Activate

Case shp(2)
ws(3).Activate

End Select

End Sub

Upvotes: 0

Views: 334

Answers (1)

LBPLC
LBPLC

Reputation: 1571

There is a much easier way to do "buttons" in VBA (I assume this is what your trying to achieve)

First off, in a module, create the "Open Worksheet" code:

Sub Open_Sheet2
Sheets("Sheet2").visible = True
Sheets("Sheet2").Activate
End Sub

Then right click your shape, choose Assign Macro and assign Open_Sheet2 to that shape. Now when it is clicked, it will open Sheet2

Upvotes: 1

Related Questions