Locate a shape in a workbook
A blog reader asks:
I need an excel 2010 macro, where I can enter a number (value) into cell B1, click on button below, and the page will automatically go to the corresponding shape, with that same number.
Answer:
- Enter a number in cell B1.
- Click "Find shape" button
The macro finds a shape.
VBA Code
Sub FindShape()
Dim sht As Worksheet
Dim shp As Shape
For Each sht In ActiveWorkbook.Worksheets
For Each shp In sht.Shapes
If shp.TextFrame.Characters.Text = Worksheets("Sheet1").Range("B1").Value Then
sht.Activate
shp.Select
ActiveWindow.ScrollRow = shp.TopLeftCell.Row
ActiveWindow.ScrollColumn = shp.TopLeftCell.Column
Exit Sub
End If
Next shp
Next sht
End Sub
Download excel *.xlsm file
Locate a shape.xlsm
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The [โฆ]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: [โฆ]
One Response to โLocate a shape in a workbookโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
This code was exactly what I needed, however my sheet has more than just textboxes on it (i.e. straight connectors) and I get a runtime error 438 when I step through the code and it comes to this type of shape. How would the code need to be written to avoid the straight connector shapes?
Thanks,
Chaney (VBA newbie)