Excel - Autoshape从单元格（值）获取它的名称 [英] Excel - Autoshape get it's name from cell (value)

问题描述

我将尝试解释这个

我已经根据在 TEXT 中选择的值选择了VBA，您可以选择形状（如圆形，三角形，正方形）和形状编号（1.2.3），当您双击时，它会立即转到下一张名为 shape 的工作表，并找到基于您选择的值的形状

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

示例：表单 TEXT > K13 在下拉框中，选择 1 中的 L13 中的圆圈
。
然后在J13中双击并且基于K13和L13，它将转到表格 SHAPES ，并选择名称为 Circle1 的形状

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

这样做很好，因为每个形状名称（如circle1，circle2，triangle1，traingle2，square1，square2）都匹配了您可以从形状列表中选择的所有组合。

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

问题：如果我因为某些原因希望在圆圈中改变名字，三角形可以说家庭，公寓，商店......然后VBA找不到名称，我必须更改所有形状的名称以匹配新的名称....

Problem: 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....

解决方案：我需要的是所有形状自动更改它的名称，如果Circle更改为家庭等..所有圈子将更改为家庭...

Solution: 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...

实际上每个形状寻找它的特定单元格的名称...
示例：circle1使用名称从B9 + C9，circle2 B9 + C10，triangle1 B10 + C9，triangle2 B10 + C10，square1 B11 + C9，square2 B11 + C10所以如果B9中的圈子改为主，所有的圆圈形状名称都会变回家，如home1，home2。

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.

行 - 列B形 - C列号

rows -column B shape - Column C number

row9 - 圈 - 1

row9 - Circle - 1

row10 - 三角形 - 2

row10 - Triangle - 2

row11 - Square - 3

row11 - Square - 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

结束子

谢谢

推荐答案

你可以运行这样的代码。我的代码（xl2010）假设您插入了这些形状tyoes

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

• 圆形自动形状椭圆形


• 从autoshapeRectangle的Square>


• 自动形状Isosceles Triangle的三角形

代码查看A8：C11中的主范围，我以扩展1列的形式提供一个
1）形状类型
2）形状数字
3）编号系统
（见下面的图片）

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).

所以下面的代码最多可以指定三个圈子
home1
home2
home3

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

最多三个方块作为
square1
square2
square3

up to three squares as square1 square2 square3

等

你可以跑当您想要手动执行此代码，或者在每次名称范围表中的单元格更改时，或者当您激活此工作表时，都会自动运行该代码。

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.pngalt =在此输入图像说明>

这篇关于Excel - Autoshape从单元格（值）获取它的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！