AW: Userform auswählen
26.07.2003 15:34:22
Nepumuk
Hallo Thomas,
hier mal ein Beispiel. Lass das testmakro laufen, und schau dir das Ergebnis an. Den Code der generiert wird, kannst dann selbst einsetzten.
Option Explicit
Private Sub Makro_generieren(sMakro As String)
Dim vbc As Object, iCounter As Integer, fMacro As String, mFound As Boolean
For Each vbc In ThisWorkbook.VBProject.VBComponents
With vbc.CodeModule
For iCounter = 1 To .CountOfLines
If .ProcOfLine(iCounter, 0) > "" Then
fMacro = .ProcOfLine(iCounter, 0)
If fMacro = sMakro Then
mFound = True
Exit For
End If
End If
Next iCounter
If mFound Then Exit For
End With
Next vbc
If Not mFound Then
With ThisWorkbook.VBProject.VBComponents(ThisWorkbook.VBProject.VBE.ActiveCodePane.CodeModule.Parent.Name).CodeModule
.InsertLines .CountOfLines + 2, "Private Sub " & sMakro & "()"
.InsertLines .CountOfLines + 1, " Dim Tabelle As Worksheet"
.InsertLines .CountOfLines + 1, " For Each Tabelle In Worksheets"
.InsertLines .CountOfLines + 1, " Tabelle.Protect Password:=" & Chr(34) & "Kennwort" & Chr(34) & ", UserInterfaceOnly:=True"
.InsertLines .CountOfLines + 1, " Tabelle.EnableOutlining = True"
.InsertLines .CountOfLines + 1, " Tabelle.EnableAutoFilter = True"
.InsertLines .CountOfLines + 1, " Next"
.InsertLines .CountOfLines + 1, "End Sub"
End With
End If
End Sub
Public Sub test()
Call Makro_generieren("XXX")
End Sub
Code eingefügt mit: Excel Code Jeanie
Gruß
Nepumuk