Makro hält an ohne Haltepunkt
24.05.2021 11:50:57
yogi
Folgendes Problem: Eine grosse Exceldatei mit vielen Tabellen und Userforms. Steuerelement referenzieren Daten in den verschiedenen Tabellen, Beispiel:
TextBox1
ControlSource "=Tabelle1!C18"
Nun sollen diese Referenzen aus allen Steuerelementen aller Userforms ausgelesen werden.
Mit folgendem Code in der Initialisierungssub der Form geht das
Private Sub UserForm_Initialize()
Dim con As Control
Dim referenz As String
Dim control_name As String
Dim type_is As String
For Each con In UserForm1.Controls
control_name = con.Name
type_is = TypeName(con)
Select Case type_is
Case "TextBox", "CheckBox", "OptionButton", "ComboBox"
referenz = con.ControlSource
Case otherweise
End Select
On Error Resume Next
referenz = ""
Next
End Sub
Sub get_referenz()
Dim wbk_name As String
Dim vbc As Object
Dim userform_name As String
Dim steuer_element As Object
Dim referenz As String
Dim control_name As String
Dim type_is As String
wbk_name = "datei_xyz.xlsm"
For Each vbc In Workbooks(wbk_name).VBProject.VBComponents
If InStr(1, vbc.Name, "Tabelle", vbTextCompare) = 0 Then
If vbc.Type = 3 Then
userform_name = vbc.Name
For Each steuer_element In ?
control_name = con.Name
type_is = TypeName(con)
Select Case type_is
Case "TextBox", "CheckBox", "OptionButton", "ComboBox"
referenz = con.ControlSource
Case otherweise
End Select
Next
End If ' vbc.Type = 1 Or vbc.Type = 100
End If ' InStr(1, vbc.Name, "Tabelle", vbTextCompare) = 0
Next vbc ' Each vbc In ActiveWorkbook.VBProject.VBComponents
End Sub
yogi