Lösungsvorschlag
12.03.2004 14:28:02
Christoph
Hallo Frank,
ich denke, so müsste es laufen (musste mir ja ziemlich das Gehirn verbiegen...)
Das Makro sortiert alle Schaltflächen des Typs "Button" nach deren Beschriftung und ordnet sie in einer Spalte an.
viele Grüße
Christoph Meffert
Option Explicit
Sub sortBtnCaption()
Dim arr1() As String, arr2() As String
Dim Dummy1 As String, Dummy2 As String
Dim i As Integer, k As Integer, a As Integer, b As Integer
Dim Sh As Shape
Dim ShCount As Integer
Dim strName As String
ShCount = ActiveSheet.Shapes.Count
For i = 1 To ShCount
Set Sh = ActiveSheet.Shapes(i)
If Sh.Name Like "Button*" Then
Sh.Select
strName = Selection.Characters.Text
k = k + 1
ReDim Preserve arr1(k)
ReDim Preserve arr2(k)
arr1(k) = strName
arr2(k) = Sh.Name
End If
Next i
For a = 1 To UBound(arr1())
For b = a To UBound(arr1())
If arr1(a) > arr1(b) Then
Dummy1 = arr1(a)
Dummy2 = arr2(a)
arr1(a) = arr1(b)
arr2(a) = arr2(b)
arr1(b) = Dummy1
arr2(b) = Dummy2
End If
Next b
Next a
For i = 1 To UBound(arr1())
Set Sh = ActiveSheet.Shapes(arr2(i))
With Sh
.Left = 200
.Top = 30 * i
.Width = 100
.Height = 25
End With
Next i
End Sub