如何选择所有名称中包含特定单词的形状?

huangapple go评论85阅读模式
英文:

how to select all shape with a specific word in their name

问题

选择并隐藏所有名称中包含特定单词的形状。

你好,

我正在使用Excel制作地图。我有一个宏,可以在地图的右上角插入一个图钉/新形状,然后您需要手动将其拖动到地图中的正确位置。该宏还会为所有图钉分配一个新名称,因此每个插入的新图钉/形状都有一个定义的名称。例如,A23_AXR42_Towncar

A23- 是形状的顺序号(即插入的第23个形状)
AXR42_ 是汽车的ID编号
Towncar- 是类别

总共将有7种不同类别的图钉。新的图钉将逐步添加。我需要一种方法来选择一个工作表中所有名称中包含单词"Towncar"的形状。因此,当我单击按钮时,它会选择所有形状,然后使用窗格窗口隐藏/显示它们。

这是否是可能的事情?

对于与VBA相关的所有内容,我完全是新手。但我尝试录制一个宏,选择所有名称中包含特定单词的形状,但这并没有真正起作用。

以下是我从录制宏中获得的代码。问题在于它只是根据名称选择所有形状,我需要它选择所有名称中包含"towncar"的形状。

  1. Sub Select_towncar_shapes()
  2. '
  3. ' Select_towncar_shapes Makro
  4. '
  5. '
  6. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar")).Select
  7. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar", "A23_ZWE18_Towncar")). _
  8. Select
  9. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar", "A23_ZWE18_Towncar", _
  10. "A20_VBV77_Towncar")).Select
  11. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar", "A23_ZWE18_Towncar", _
  12. "A20_VBV77_Towncar", "A24_RTC53_Towncar")).Select
  13. ActiveSheet.Shapes.Range(Array("A24_RTC53_Towncar")).Visible = msoFalse
  14. ActiveSheet.Shapes.Range(Array("A23_ZWE18_Towncar")).Visible = msoFalse
  15. ActiveSheet.Shapes.Range(Array("A20_VBV77_Towncar")).Visible = msoFalse
  16. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar")).Visible = msoFalse
  17. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar")).Visible = msoTrue
  18. ActiveSheet.Shapes.Range(Array("A20_VBV77_Towncar")).Visible = msoTrue
  19. ActiveSheet.Shapes.Range(Array("A23_ZWE18_Towncar")).Visible = msoTrue
  20. ActiveSheet.Shapes.Range(Array("A24_RTC53_Towncar")).Visible = msoTrue
  21. End Sub

希望这可以帮助你选择并隐藏包含"towncar"的所有形状。

英文:

Select and hide all shapes with at specific word in their name.

Hello,

Im making a map using excel. I have a macro that inserts a pin/ new shape in the top right corner of the map that you then need to manually move by draging it to the right place in the map. the macro also gives all the pins a new name so each new pin/shape that is inserted has a difined name. For example A23_AXR42_Towncar

A23- is the sequential order of the shape (i.e this was the 23 shape that was inserted)
AXR42_ is the id number of the car
Towncar- is the category

In total there will be 7 different categories of pins. and new pins will be added along the way. I need a way to select all shapes in one sheet that has the word Towncar in their name. So when i click a button it selects all the shapes and then hide/show them using the pane window.

Is this a thing that is possible?

i completely new to everything that has with VBA to do. But i have tried to record a macro by choosing all shapes containing a certain word in the name. but this didnt really work.

Here is the code that i gotfrom recording a macro. The problem is that it just select all shapes bsed on their name. i need it to choose all shapes that have "towncar" in the name

  1. Sub Select_towncar_shapes()
  2. '
  3. ' Select_towncar_shapes Makro
  4. '
  5. '
  6. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar")).Select
  7. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar", "A23_ZWE18_Towncar")). _
  8. Select
  9. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar", "A23_ZWE18_Towncar", _
  10. "A20_VBV77_Towncar")).Select
  11. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar", "A23_ZWE18_Towncar", _
  12. "A20_VBV77_Towncar", "A24_RTC53_Towncar")).Select
  13. ActiveSheet.Shapes.Range(Array("A24_RTC53_Towncar")).Visible = msoFalse
  14. ActiveSheet.Shapes.Range(Array("A23_ZWE18_Towncar")).Visible = msoFalse
  15. ActiveSheet.Shapes.Range(Array("A20_VBV77_Towncar")).Visible = msoFalse
  16. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar")).Visible = msoFalse
  17. ActiveSheet.Shapes.Range(Array("A19_XRT44_Towncar")).Visible = msoTrue
  18. ActiveSheet.Shapes.Range(Array("A20_VBV77_Towncar")).Visible = msoTrue
  19. ActiveSheet.Shapes.Range(Array("A23_ZWE18_Towncar")).Visible = msoTrue
  20. ActiveSheet.Shapes.Range(Array("A24_RTC53_Towncar")).Visible = msoTrue
  21. End Sub

答案1

得分: 3

以下是已翻译的内容:

  1. 下面的例程将显示或隐藏工作表中包含特定单词的形状。无需手动*选择*任何形状并设置可见性:
  2. Sub ShowHideShapes(ws As Worksheet, word As String, show As Boolean)
  3. Dim sh As Shape
  4. For Each sh In ws.Shapes
  5. If InStr(1, sh.Name, word, vbTextCompare) > 0 Then
  6. sh.Visible = show
  7. End If
  8. Next
  9. End Sub
  10. 现在在工作表上放置2个按钮,一个用于显示形状,一个用于隐藏形状。这些按钮的代码如下:
  11. Sub buttonShow_click()
  12. ShowHideShapes ActiveSheet, "Towncar", True
  13. End Sub
  14. Sub buttonHide_click()
  15. ShowHideShapes ActiveSheet, "Towncar", False
  16. End Sub
  17. 请注意,您还可以使用形状来执行宏,只需将它们放在工作表上,右键单击它们,然后使用“分配宏”。
  18. 如果您想显示和隐藏其他具有不同名称的形状,只需将第二个参数从`"Towncar"`更改为您想要显示或隐藏的内容。
英文:

The following routine will show or hide all shapes of a worksheet where the shape name contain a specific word. There is no need to select any shape and set the visibility manually:

  1. Sub ShowHideShapes(ws As Worksheet, word As String, show As Boolean)
  2. Dim sh As Shape
  3. For Each sh In ws.Shapes
  4. If InStr(1, sh.Name, word, vbTextCompare) > 0 Then
  5. sh.Visible = show
  6. End If
  7. Next
  8. End Sub

Now put 2 buttons on your sheet, one to show and one to hide the shapes. The code for this buttons would be

  1. Sub buttonShow_click()
  2. ShowHideShapes ActiveSheet, "Towncar", True
  3. End Sub
  4. Sub buttonHide_click()
  5. ShowHideShapes ActiveSheet, "Towncar", False
  6. End Sub

Note that you can also use shapes to execute macros, just place them on the sheet, right click on it and use "Assign Macro".

If you want to show and hide other shapes with different names, simply change the 2nd parameter from "Towncar" to whatever you want to show or hide.

答案2

得分: 2

显示/隐藏形状

调用过程

  1. Sub ShowTownCar()
  2. Dim wb As Workbook: Set wb = ThisWorkbook ' 包含此代码的工作簿
  3. ShowHideShapes "TownCar", wb, "Sheet1"
  4. End Sub
  5. Sub HideTownCar()
  6. Dim wb As Workbook: Set wb = ThisWorkbook ' 包含此代码的工作簿
  7. ShowHideShapes "TownCar", wb, "Sheet1", True
  8. End Sub

被调用的过程(方法)

  1. Sub ShowHideShapes( _
  2. ByVal EndsWith As String, _
  3. ByVal wb As Workbook, _
  4. Optional ByVal WorksheetName As String = "", _
  5. Optional ByVal DoHide As Boolean = False)
  6. Dim ws As Worksheet:
  7. If Len(WorksheetName) = 0 Then
  8. Set ws = wb.ActiveSheet
  9. Else
  10. Set ws = wb.Sheets(WorksheetName)
  11. End If
  12. Dim eLen As Long: eLen = Len(EndsWith)
  13. Dim dict As Object, shp As Shape, shpName As String, IsFirstFound As Boolean
  14. For Each shp In ws.Shapes
  15. shpName = shp.Name
  16. If Len(shpName) >= eLen Then
  17. If StrComp(Right(shp.Name, eLen), EndsWith, vbTextCompare) = 0 Then
  18. If Not IsFirstFound Then
  19. Set dict = CreateObject("Scripting.Dictionary")
  20. dict.CompareMode = vbTextCompare
  21. IsFirstFound = True
  22. End If
  23. dict(shpName) = Empty
  24. End If
  25. End If
  26. Next shp
  27. If dict Is Nothing Then
  28. MsgBox "未找到以" & EndsWith & "结尾的形状。", _
  29. vbExclamation
  30. Exit Sub ' 未找到形状
  31. End If
  32. ws.Shapes.Range(dict.Keys).Visible = Not DoHide
  33. End Sub
英文:

Show/Hide Shapes

The Calling Procedures

<!-- language: lang-vb -->

  1. Sub ShowTownCar()
  2. Dim wb As Workbook: Set wb = ThisWorkbook &#39; workbook containing this code
  3. ShowHideShapes &quot;TownCar&quot;, wb, &quot;Sheet1&quot;
  4. End Sub
  5. Sub HideTownCar()
  6. Dim wb As Workbook: Set wb = ThisWorkbook &#39; workbook containing this code
  7. ShowHideShapes &quot;TownCar&quot;, wb, &quot;Sheet1&quot;, True
  8. End Sub

The Called Procedure (Method)

<!-- language: lang-vb -->

  1. Sub ShowHideShapes( _
  2. ByVal EndsWith As String, _
  3. ByVal wb As Workbook, _
  4. Optional ByVal WorksheetName As String = &quot;&quot;, _
  5. Optional ByVal DoHide As Boolean = False)
  6. Dim ws As Worksheet:
  7. If Len(WorksheetName) = 0 Then
  8. Set ws = wb.ActiveSheet
  9. Else
  10. Set ws = wb.Sheets(WorksheetName)
  11. End If
  12. Dim eLen As Long: eLen = Len(EndsWith)
  13. Dim dict As Object, shp As Shape, shpName As String, IsFirstFound As Boolean
  14. For Each shp In ws.Shapes
  15. shpName = shp.Name
  16. If Len(shpName) &gt;= eLen Then
  17. If StrComp(Right(shp.Name, eLen), EndsWith, vbTextCompare) = 0 Then
  18. If Not IsFirstFound Then
  19. Set dict = CreateObject(&quot;Scripting.Dictionary&quot;)
  20. dict.CompareMode = vbTextCompare
  21. IsFirstFound = True
  22. End If
  23. dict(shpName) = Empty
  24. End If
  25. End If
  26. Next shp
  27. If dict Is Nothing Then
  28. MsgBox &quot;No shapes ending with &quot;&quot;&quot; &amp; EndsWith &amp; &quot;&quot;&quot; found.&quot;, _
  29. vbExclamation
  30. Exit Sub &#39; no shapes found
  31. End If
  32. ws.Shapes.Range(dict.Keys).Visible = Not DoHide
  33. End Sub

答案3

得分: 1

我尽量使它尽可能简单。
最好将名称作为参数传递,但对于初学者来说,这个更容易。

Public Sub SelectShapesByName()
Dim i As Long
Dim f() As Variant, z As Long
Dim StepField As Long
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errExit
StepField = 5
ReDim f(StepField)
z = -1
For i = 1 To ws.Shapes.Count
On Error Resume Next
If ws.Shapes(i).Type <> 4 Then
If ws.Shapes(i).Name Like "Towncar" Then ' 这里是名称
If Err.Number = 0 Then
z = z + 1
If z > StepField Then
StepField = StepField + 5
ReDim Preserve f(StepField)
End If
f(z) = i
End If
End If
End If
Next i

  1. On Error GoTo errExit
  2. If z > -1 Then
  3. If UBound(f) <> z Then ReDim Preserve f(z)
  4. ws.Shapes.Range(f).Select
  5. End If
  6. Exit Sub

errExit:
End Sub

英文:

I made it as easy as possible.
Better to give the name as argument but for a beginner this one is easier

  1. Public Sub SelectShapesByName()
  2. Dim i As Long
  3. Dim f() As Variant, z As Long
  4. Dim StepField As Long
  5. Dim ws As Worksheet
  6. Set ws = ActiveSheet
  7. On Error GoTo errExit
  8. StepField = 5
  9. ReDim f(StepField)
  10. z = -1
  11. For i = 1 To ws.Shapes.Count
  12. On Error Resume Next
  13. If ws.Shapes(i).Type &lt;&gt; 4 Then
  14. If ws.Shapes(i).Name Like &quot;*Towncar*&quot; Then &#39; Here is the name
  15. If Err.Number = 0 Then
  16. z = z + 1
  17. If z &gt; StepField Then
  18. StepField = StepField + 5
  19. ReDim Preserve f(StepField)
  20. End If
  21. f(z) = i
  22. End If
  23. End If
  24. End If
  25. Next i
  26. On Error GoTo errExit
  27. If z &gt; -1 Then
  28. If UBound(f) &lt;&gt; z Then ReDim Preserve f(z)
  29. ws.Shapes.Range(f).Select
  30. End If
  31. Exit Sub
  32. errExit:
  33. End Sub

答案4

得分: 1

尝试这个:

  1. Sub select_towncar_shapes()
  2. select_shapes "towncar"
  3. End Sub
  4. Sub hide_towncar_shapes()
  5. shapes_visibility "towncar", False
  6. End Sub
  7. Sub show_towncar_shapes()
  8. shapes_visibility "towncar", True
  9. End Sub
  10. Sub shapes_visibility(includedStr As String, is_visible As Boolean)
  11. Dim sht As Worksheet: Set sht = ThisWorkbook.Sheets("Sheet3")
  12. Dim shapes_array() As String: shapes_array = get_shapes_array(sht, includedStr)
  13. On Error Resume Next
  14. sht.Shapes.Range(shapes_array).Visible = is_visible
  15. End Sub
  16. Sub select_shapes(includedStr As String)
  17. Dim sht As Worksheet: Set sht = ThisWorkbook.Sheets("Sheet3")
  18. Dim shapes_array() As String: shapes_array = get_shapes_array(sht, includedStr)
  19. On Error Resume Next
  20. sht.Shapes.Range(shapes_array).Select
  21. End Sub
  22. Function get_shapes_array(sht As Worksheet, includedStr As String) As String()
  23. Dim shp As Shape
  24. Dim output() As String
  25. Dim arrayCount As Long
  26. For Each shp In sht.Shapes
  27. If InStr(1, shp.Name, includedStr, vbTextCompare) > 0 Then
  28. arrayCount = arrayCount + 1
  29. ReDim Preserve output(1 To arrayCount)
  30. output(arrayCount) = shp.Name
  31. End If
  32. Next shp
  33. get_shapes_array = output
  34. End Function
英文:

Try this:

  1. Sub select_towncar_shapes()
  2. select_shapes &quot;towncar&quot;
  3. End Sub
  4. Sub hide_towncar_shapes()
  5. shapes_visibility &quot;towncar&quot;, False
  6. End Sub
  7. Sub show_towncar_shapes()
  8. shapes_visibility &quot;towncar&quot;, True
  9. End Sub
  10. Sub shapes_visibility(includedStr As String, is_visible As Boolean)
  11. Dim sht As Worksheet: Set sht = ThisWorkbook.Sheets(&quot;Sheet3&quot;)
  12. Dim shapes_array() As String: shapes_array = get_shapes_array(sht, includedStr)
  13. On Error Resume Next
  14. sht.Shapes.Range(shapes_array).Visible = is_visible
  15. End Sub
  16. Sub select_shapes(includedStr As String)
  17. Dim sht As Worksheet: Set sht = ThisWorkbook.Sheets(&quot;Sheet3&quot;)
  18. Dim shapes_array() As String: shapes_array = get_shapes_array(sht, includedStr)
  19. On Error Resume Next
  20. sht.Shapes.Range(shapes_array).Select
  21. End Sub
  22. Function get_shapes_array(sht As Worksheet, includedStr As String) As String()
  23. Dim shp As Shape
  24. Dim output() As String
  25. Dim arrayCount As Long
  26. For Each shp In sht.Shapes
  27. If InStr(1, shp.Name, includedStr, vbTextCompare) &gt; 0 Then
  28. arrayCount = arrayCount + 1
  29. ReDim Preserve output(1 To arrayCount)
  30. output(arrayCount) = shp.Name
  31. End If
  32. Next shp
  33. get_shapes_array = output
  34. End Function

huangapple
  • 本文由 发表于 2023年6月1日 15:42:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/76379677.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定