Hi Allen,
Jeff's code worked beautifully, however, I modified it a little to fit into
what I'm doing. Basically, I did the following:
1. Instead of the code creating the macro list action string for the Row
Source for the Value List of the combo box (which would be in the order it
was read from the macro and not necessarily in alphabetical order), I stored
each macro's action to a Table so I could then read it in as the combo box's
Row Source and alphabetize it. Therefore, I changed the combo box's Row
Source Type to Table/Query and it's Row Source to a SQL statement which
read's in the Table.
2. The Table's name is USysRibbons - Macro List and it only has one field:
Macro Name. The combo box's Row Source is: "SELECT [USysRibbons - Macro
List].[Macro Name] FROM [USysRibbons - Macro List] ORDER BY [USysRibbons -
Macro List].[Macro Name];".
3. I changed Jeff's Function to a Sub which is called in the Form's Open
event instead of putting the resultant string in the combo box's Row Source.
4. I also changed the name of the procedure to fit more into my style of
coding.
My modifed code is below (my changes are identified in the comments wherever
a change is made):
Public Sub CreateMacroList()
On Error GoTo ErrorPoint
' Code by Access MVP M.L. "Sco" Scofield
'
http://www.scobiz.com
' And by Jeff Conrad - Access Junkie
' Copyright © 2005 Conrad Systems Development
' It is not to be altered or distributed,
' except as part of an application.
'
' Code will produce a list of all macro group names and
' internal names formatted to match the syntax found in
' all code Event Procedure lists
'
' This code will work with Access versions 2000, 2002, and 2003
' You must also set a reference to the DAO object library
'
' You are free to use this code in any projects providing
' you agree to the following two conditions:
' 1. This copyright information remains intact
' 2. You admit you are an Access Junkie
' (Why else would you be looking at this?)
'I already have this as a Public variable
'Dim dbs As DAO.Database
Dim rstMacro As Recordset
Dim intFileIn As Integer
Dim strRow As String
Dim intCount As Integer
'I commented this out because I'm using the Table instead of creating
the combo box's
'Row Source string.
'Dim strMacroNames As String
Dim strTempFileName As String
Dim blShowHidden As Boolean
Dim blIsHidden As Boolean
Dim strName As String
' Are we supposed to show hidden objects?
blShowHidden = Application.GetOption("Show Hidden Objects")
' I commented this out because it's taken care of publicly in my code
' Return reference to current database.
'Set dbs = CurrentDb()
'I added this
'delete the prior Macro List
DoCmd.RunSQL "DELETE [USysRibbons - Macro List].* FROM [USysRibbons -
Macro List]"
'I added this
Set rstMacro = dbs.OpenRecordset("USysRibbons - Macro List")
'I commented this out since I'm using the above Table to store and sort
the list
'strMacroNames = ""
' Create a temp file in Windows Temp directory
strTempFileName = GetTempFile()
' Set the loop for the number of macro object in database
For intCount = 0 To dbs.Containers("scripts").Documents.count - 1
' Capture the name of this macro object
strName = dbs.Containers("Scripts").Documents(intCount).Name
'I commented this out and replaced it with the simpler code line
below it
' Determine if this macro has been set to hidden
'If Application.GetHiddenAttribute(acMacro, strName) = True Then
' blIsHidden = True
'Else
' blIsHidden = False
'End If
blIsHidden = Application.GetHiddenAttribute(acMacro, strName)
' Now determine if this macro has been deleted AND whether we should
' hide it if it has been set to Hidden based on user's properties
setting
If Not (Left(strName, 7) = "~TMPCLP") And (blIsHidden Imp
blShowHidden) Then
' Everything looks good so we are OK to proceed
' Export this macro to a text file
SaveAsText acMacro, strName, strTempFileName
' Open the text file we made from the macro
intFileIn = FreeFile
Open strTempFileName For Input As intFileIn
' I commented this out since I'm using the Table instead and
because I did
' NOT want the macro's name included in the list - only the
macro's actions
' Capture the name of this current macro object and add to list
'strMacroNames = strMacroNames & ";" & strName
' Loop through text file looking for all instances of "MacroName="
' Print the name of any internal macro names by stripping out
' the preceeding information and the end quotation mark.
' Add the name of the current macro object and a period before
' the name of each macro group
Do Until EOF(intFileIn)
Line Input #intFileIn, strRow
If InStr(strRow, "MacroName =") <> 0 Then
strRow = Mid(strRow, 17)
'I added this If statement so the list does NOT include
the menu actions
'with "-" as it's name since those serve as menu
separators
If Left(strRow, 1) <> "-" Then
strRow = strName & "." & strRow
strRow = Left(strRow, Len(strRow) - 1)
'I commented this out since I'm using the Table
instead
'strMacroNames = strMacroNames & ";" & strRow
rstMacro.AddNew
rstMacro![Macro Name] = strRow
rstMacro.Update
End If
End If
Loop
'Close text file
Close intFileIn
Else
' If we reach here it is because current macro has either been
' 1. Deleted
' 2. Set to hidden AND Hide Hidden Objects has been set to True
' So we skip over all the above code and move on to the next one
End If
' Start next macro
Next intCount
' I commented this out since I'm using the Table instead
' Strip out leading semi colon to arrive at complete formatted list
'CreateMacroList = Mid(strMacroNames, 2)
'I added this
rstMacro.Close
ExitPoint:
' Cleanup Code
On Error Resume Next
'I commented this out because I use dbs throughout the project
'Set dbs = Nothing
' Delete the temp file we created
Kill strTempFileName
Exit Sub
ErrorPoint:
' Display error message if an unexpected error occurs
' Exit the procedure through our cleanup code
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
