2338

Excel - Autoshape get it's name from cell (value)

I will try to explain this

I have VBA that based on value selected in sheet called <strong>TEXT</strong> you can select <strong>shape</strong> (like Circle, triangle, square) and <strong>shape number</strong> (1.2.3) and when you double click it immediately goes to next sheet called <strong>shapes</strong> and find that shape based on values you selected

<strong>Example</strong>: sheet <strong>TEXT</strong> in Cell <strong>K13</strong> in drop-box select <strong>circle</strong> in cell <strong>L13</strong> in drop-box select number <strong>1</strong>. then double click in J13 and based on K13 and L13 it goes to sheet <strong>SHAPES</strong> and select shape that has name <strong>Circle1</strong>

This works fine because each shapes name (like circle1, circle2, triangle1, traingle2, square1, square2) match all combination that you can select from shape list ..

<strong>Problem</strong>: If I for some reason want to change names in drop-box from circle, triangle, quare to let say home, apartment, shop... then VBA can't find that names and I have to change names for all shapes to match new names....

<strong>Solution</strong>: What I need is that all shapes automatically change it's name so if Circle is changed to home etc.. all circle will change to home...

actually each shape looking for it's name from specific cells... example: circle1 uses it's name from B9+C9, circle2 B9+C10, triangle1 B10+C9, triangle2 B10+C10, square1 B11+C9, square2 B11+C10.. so if circle in B9 is changed to home all circle shape names will change to home, like home1, home2.

rows -<strong>column B shape</strong> - Column C number

row9 - <strong>Circle</strong> - 1

row10 - <strong>Triangle</strong> - 2

row11 - <strong>Square</strong> - 3

VBA Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim test As String If Not Intersect(Target, Range("J13:J16")) Is Nothing Then test = Target.Offset(, 1).Value & Target.Offset(, 2).Value Worksheets("Shapes").Shapes(CStr(test)).Select Worksheets("Shapes").Activate End If

End Sub

Thank you

Answer1:

You could run code like this. My code (xl2010) assumes that you inserted these shape tyoes

    <li>Circle from the autoshape "Oval"</li> <li>Square from the autoshape "Rectangle"</li> <li>Triangle from the autoshape "Isosceles Triangle"</li> </ul>

    The code looks at a master range in A8:C11 that I expanded by 1 column form your example to provide a 1) Shape type 2) Shape number 3) Numbering system (see pic below)

    The code when run looks at each shape on the sheet, tests if it is a circle, square or rectangle, looks up the name in the second column of the table, then applies the number of the position in the third column (note that you may need to add more numbers and extend this range).

    So the code below names up to three circles as home1 home2 home3

    up to three squares as square1 square2 square3

    etc

    You could either run this code when you wanted to manually, or run it automatically with events each time a cell in the name ranging table changes, or when you activtated this sheets etc

    Sub ReName() Dim shp As Shape Dim rng1 As Range Dim lngCirc As Long Dim lngSq As Long Dim lngTri As Long Set rng1 = Sheets(1).Range("A8:C18") For Each shp In ActiveSheet.Shapes Select Case shp.AutoShapeType Case msoShapeOval lngCirc = lngCirc + 1 shp.Name = rng1.Cells(2, 2) & rng1.Cells(1, 3).Offset(lngCirc) Case msoShapeIsoscelesTriangle lngTri = lngTri + 1 shp.Name = rng1.Cells(3, 2) & rng1.Cells(1, 3).Offset(lngTri) Case msoShapeRectangle lngSq = lngSq + 1 shp.Name = rng1.Cells(4, 2) & rng1.Cells(1, 3).Offset(lngSq) Case Else Debug.Print "Check shape: " & shp.Name & " of " & shap.AutoShapeType End Select Next End Sub

    <img src="https://i.stack.imgur.com/kaib7.png" alt="enter image description here">

Recommend

  • The calling thread must be STA, because many UI components require this while navigation in wpf
  • jQuery variable is not being populated before ajax function.
  • Setting Image Button via it's id into a 8x8 2d array. Android. Xamarin
  • Why is the output of print in python2 and python3 different with the same string?
  • Locale language detection and translation?
  • Need assistance with excel macro- vba
  • PHPExcel excel read is not working for some cells with calculation
  • R add new column depending on values in a range in different columns
  • Unable to add extension to use Azure VM extensions using Ansible
  • Encircle points with shaded blobs
  • Why is my small-caps font-variant CSS class being disregarded?
  • hiding rows in excel based on words in a cell
  • Excel 2010 search for text in IF function - separate cell data
  • Entity Framework v4 and underscores in column names
  • Yiic Grid view always giving live is not a function
  • Why my session in the view control is null
  • Change ListView font size in JavaFX
  • PHP problems with current url
  • Hibernate criteria query
  • Merge the values of multiple elements and take the average of the attribute field
  • xtable - background colour of added rows
  • File extension of zlib zipped html page?
  • Failed to find version-tag string. File must be updated
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • cell spacing in div table
  • Excel's Macro-Recorder usage
  • NHibernate Validation Localization with S#arp Architecture
  • Spring Data JPA custom method causing PropertyReferenceException
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • Fetching methods from BroadcastReceiver to update UI
  • swift auto completion not working in Xcode6-Beta
  • Check if a string to interpolate provides expected placeholders
  • How to apply VCL Styles to DLL-based forms in Inno Setup?
  • vba code to select only visible cells in specific column except heading
  • RestKit - RKRequestDelegate does not exist
  • Traverse Array and Display in markup
  • Matrix multiplication with MKL
  • CSS Applying specific rule for a specific monitor resolution with only CSS is posible?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Converting MP3 duration time