Marty Plante
New Member
- Joined
- Dec 28, 2016
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
I have existing code for listing macros in active workbook that creates a new sheet. I want to have it overwrite the existing "List_of_Macros" worksheet. I've created this with codename shtMacroList and the code is in the sheet and is runs on Worksheet_Activate()
I've been unable to find this code where it leaves the original worksheet and overwrites the data with updated list of macros. Any assistance would be appreciated. I do have this working perfectly with a table of contents code.
Sub ListMacros()
Dim VBComp As VBComponent
Dim wsTarget As Worksheet
Dim StartLine As Long
Dim iRow As Integer
Application.ScreenUpdating = False
Set wsTarget = Worksheets.Add
wsTarget.Range("A1") = "Macro"
wsTarget.Range("A1").Font.Bold = True
With wsTarget.Range("A1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
iRow = 2
For Each VBComp In ThisWorkbook.VBProject.VBComponents
With VBComp.CodeModule
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
wsTarget.Cells(iRow, 1) = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iRow = iRow + 1
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next VBComp
wsTarget.Range("A1").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
I've been unable to find this code where it leaves the original worksheet and overwrites the data with updated list of macros. Any assistance would be appreciated. I do have this working perfectly with a table of contents code.
Sub ListMacros()
Dim VBComp As VBComponent
Dim wsTarget As Worksheet
Dim StartLine As Long
Dim iRow As Integer
Application.ScreenUpdating = False
Set wsTarget = Worksheets.Add
wsTarget.Range("A1") = "Macro"
wsTarget.Range("A1").Font.Bold = True
With wsTarget.Range("A1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
iRow = 2
For Each VBComp In ThisWorkbook.VBProject.VBComponents
With VBComp.CodeModule
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
wsTarget.Cells(iRow, 1) = _
.ProcOfLine(StartLine, vbext_pk_Proc)
iRow = iRow + 1
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next VBComp
wsTarget.Range("A1").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub