Code library storage? *Solved

[Hatcheda]

I am starting to have large library of code that preforms various functions.
I refer to it as needed when making new code with similar properties. It is getting a bit excessive and harder to find what I am looking for . . .

Anyone have good ideas how to catalog it for quick access? in sheets, modules, whatever . . .

 

[Legacy 98055]

Store it in modules and use this free addin to document your code.

MZ-Tools 3.0 for Visual Basic 6.0, 5.0 and VBA

An excellent free addin...

 

[Hatcheda]

trying it out

 

[Hatcheda]

Right_Click
I decided to go a different way with the storage.
I now have a workbook that will have sheets named by the type of code stored there. They will be formatted as all white with green lines after each code segment.

The code below will alphabetize each sheet for me and create a linkable index on the first page to go directly to each page.

The addin is great though and will be useful when writing code! Thanks

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Ws As Worksheet
Dim Store1, Store2, Store3, Store4, Store5, Store6, Store7, Store8, Store9, Store10 As String

Sheets("Index").Activate: Range("A1").Activate
Application.ScreenUpdating = False
'''''''''''''''''Moves the Tab names to Index and Adds the links''''''''''''''''
Set Moveon = ActiveCell
For Each Ws In Worksheets
Moveon = Ws.name
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Ws.name & "!A1", TextToDisplay:=Ws.name
Set Moveon = ActiveCell.Offset(1, 0)
Moveon.Activate
Next Ws
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

''''''''''''''Puts the Tabs in Alphabetical order''''''''''''''''''''''''''''''''''
Sheets("Index").Activate: Range("A1").Activate
On Error Resume Next
Store1 = ActiveCell: Names.Add "Name_1", Store1, False
Store2 = ActiveCell.Offset(1, 0): Names.Add "Name_2", Store2, False
Store3 = ActiveCell.Offset(2, 0): Names.Add "Name_3", Store3, False
Store4 = ActiveCell.Offset(3, 0): Names.Add "Name_4", Store4, False
Store5 = ActiveCell.Offset(4, 0): Names.Add "Name_5", Store5, False
Store6 = ActiveCell.Offset(5, 0): Names.Add "Name_6", Store6, False
Store7 = ActiveCell.Offset(6, 0): Names.Add "Name_7", Store7, False
Store8 = ActiveCell.Offset(7, 0): Names.Add "Name_8", Store8, False
Store9 = ActiveCell.Offset(8, 0): Names.Add "Name_9", Store9, False
Store10 = ActiveCell.Offset(9, 0): Names.Add "Name_10", Store10, False

Sheets([Name_9]).Move Before:=Sheets([Name_10])
Sheets([Name_8]).Move Before:=Sheets([Name_9])
Sheets([Name_7]).Move Before:=Sheets([Name_8])
Sheets([Name_6]).Move Before:=Sheets([Name_7])
Sheets([Name_5]).Move Before:=Sheets([Name_6])
Sheets([Name_4]).Move Before:=Sheets([Name_5])
Sheets([Name_3]).Move Before:=Sheets([Name_4])
Sheets([Name_2]).Move Before:=Sheets([Name_3])
Sheets([Name_1]).Move Before:=Sheets([Name_2])
Sheets("Index").Move Before:=Sheets([Name_1])
Sheets("Index").Activate: Range("A1").Activate
Application.ScreenUpdating = True
End Sub

As set, I will just have to add a few more ‘Move Before’ lines so that it will be ready for future tabs.

 

[Hatcheda]

Nix that

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Ws As Worksheet
Sheets("Index").Activate: Range("A1").Activate
Application.ScreenUpdating = False
'''''''''''''''''Moves the Tab names to Index and Adds the links''''''''''''''''
Set Moveon = ActiveCell
For Each Ws In Worksheets
Moveon = Ws.Name
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Ws.Name & "!A1", TextToDisplay:=Ws.Name
Set Moveon = ActiveCell.Offset(1, 0)
Moveon.Activate
Next Ws
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

''''''''''''''Puts the Tabs in Alphabetical order''''''''''''''''''''''''''''''''''
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
    FirstWSToSort = 2
    LastWSToSort = Worksheets.Count
Else
    With ActiveWindow.SelectedSheets
        For N = 2 To .Count
            If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                MsgBox "You cannot sort non-adjacent sheets"
                Exit Sub
            End If
        Next N
        FirstWSToSort = .Item(1).Index
        LastWSToSort = .Item(.Count).Index
     End With
End If

For M = FirstWSToSort To LastWSToSort
    For N = M To LastWSToSort
        If SortDescending = True Then
            If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
                Worksheets(N).Move Before:=Worksheets(M)
            End If
        Else
            If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
               Worksheets(N).Move Before:=Worksheets(M)
            End If
        End If
     Next N
Next M


Sheets("Index").Activate: Range("A1").Activate
Application.ScreenUpdating = True
End Sub

 

[SydneyGeek]

MZ-Tools 3.0 for Visual Basic 6.0, 5.0 and VBA

An excellent free addin...

Absolutely! Thanks for the pointer, Tom!

Denis