I am using the the following code to list modeules and procedures. It is not listing "modules" alphabetically like that are listed in the VBE project????
Here is a sample of "module" names and how they are listed when I run the code>
Print1
Clock
HyperLinks
CB_Create
CB_HideShowDelete
OnAction
WS_GetNames<pre>Sub Macro_List_All_Subs_and_Functions3()
Dim MasterVBComp As VBComponents
Dim SingleVBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Integer
Dim row As Long
Dim EndOfPageRow As Long
Dim col As Integer
Sheets("fil-Menu Maker-2").Select
'find the row for listing space
startRow = Range("A1:A100").Find("Macro Information").row + 1
row = startRow
EndOfPageRow = 70
col = 1
' Cells(startRow, 1).Resize(100, 6).Clear
' OR
With Range(Cells(startRow, 1), Cells(startRow + 100, 6))
.ClearContents
.Font.Bold = False
.IndentLevel = 0
End With
Set MasterVBComp = ThisWorkbook.VBProject.VBComponents
'module names first
For Each SingleVBComp In MasterVBComp
If SingleVBComp.Type = vbext_ct_StdModule Then
Cells(row, col) = SingleVBComp.Name
With Cells(row, col)
.Font.Bold = True
End With
row = row + 1
If row > EndOfPageRow Then
row = startRow
col = col + 2
End If
End If
'procedure names after its module name
If SingleVBComp.Type = vbext_ct_StdModule Then
Set VBCodeMod = SingleVBComp.CodeModule
StartLine = VBCodeMod.CountOfDeclarationLines + 1
Do Until StartLine >= VBCodeMod.CountOfLines
Cells(row, col) = VBCodeMod.ProcOfLine(StartLine, vbext_pk_Proc)
With Cells(row, col)
.IndentLevel = 1
End With
StartLine = StartLine + _
VBCodeMod.ProcCountLines(VBCodeMod.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
row = row + 1
If row > EndOfPageRow Then
row = startRow
col = col + 2
End If
Loop
End If
Next
End Sub</pre>
This message was edited by em on 2002-11-04 08:17
This message was edited by em on 2003-02-01 00:03
Here is a sample of "module" names and how they are listed when I run the code>
Print1
Clock
HyperLinks
CB_Create
CB_HideShowDelete
OnAction
WS_GetNames<pre>Sub Macro_List_All_Subs_and_Functions3()
Dim MasterVBComp As VBComponents
Dim SingleVBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Integer
Dim row As Long
Dim EndOfPageRow As Long
Dim col As Integer
Sheets("fil-Menu Maker-2").Select
'find the row for listing space
startRow = Range("A1:A100").Find("Macro Information").row + 1
row = startRow
EndOfPageRow = 70
col = 1
' Cells(startRow, 1).Resize(100, 6).Clear
' OR
With Range(Cells(startRow, 1), Cells(startRow + 100, 6))
.ClearContents
.Font.Bold = False
.IndentLevel = 0
End With
Set MasterVBComp = ThisWorkbook.VBProject.VBComponents
'module names first
For Each SingleVBComp In MasterVBComp
If SingleVBComp.Type = vbext_ct_StdModule Then
Cells(row, col) = SingleVBComp.Name
With Cells(row, col)
.Font.Bold = True
End With
row = row + 1
If row > EndOfPageRow Then
row = startRow
col = col + 2
End If
End If
'procedure names after its module name
If SingleVBComp.Type = vbext_ct_StdModule Then
Set VBCodeMod = SingleVBComp.CodeModule
StartLine = VBCodeMod.CountOfDeclarationLines + 1
Do Until StartLine >= VBCodeMod.CountOfLines
Cells(row, col) = VBCodeMod.ProcOfLine(StartLine, vbext_pk_Proc)
With Cells(row, col)
.IndentLevel = 1
End With
StartLine = StartLine + _
VBCodeMod.ProcCountLines(VBCodeMod.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
row = row + 1
If row > EndOfPageRow Then
row = startRow
col = col + 2
End If
Loop
End If
Next
End Sub</pre>
This message was edited by em on 2002-11-04 08:17
This message was edited by em on 2003-02-01 00:03