Howdy Jchicoine, here's one way of stacking a combobox on your first indexed sheet. I would run it it through
WrkWRefs which will call the second procedure to ensure that your MS Visual Basic for Applications Extensibility library is indeed installed.<pre>
Sub WrkWRefs()
Dim n As Integer, p As String
For n = 1 To ThisWorkbook.VBProject.References.Count
p = ActiveWorkbook.VBProject.References.Item
.Description
If InStr(p, "Visual Basic for Applications Extensibility") Then _
GoTo 1
Next n
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 1, 0
1: Call ProcLst
End Sub
Private Sub ProcLst()
Dim i As Integer, cnt As Integer, f As Integer, MyProc As String
Dim z As String
Application.EnableEvents = False
Sheets(1).ComboBox1.clear
For Each VBComponent In ThisWorkbook.VBProject.VBComponents
With VBComponent.CodeModule
For i = 1 To .CountOfLines
If .ProcOfLine(i, vbext_pk_Proc) > "" Then
MyProc = .ProcOfLine(i, vbext_pk_Proc)
cnt = 0
For f = 0 To Sheets(1).ComboBox1.ListCount - 1
z = Sheets(1).ComboBox1.List(f)
If z = MyProc Then cnt = cnt + 1
Next f
If cnt = 0 Then
If MyProc<> "WrkWRefs" And MyProc<> "ProcLst" _
And MyProc<> "ComboBox1_Change" Then _
Sheets(1).ComboBox1.AddItem MyProc
End If
End If
Next i
End With
Next VBComponent
Sheets(1).ComboBox1.Value = Sheets(1).ComboBox1.List(0)
Application.EnableEvents = True
End Sub</pre>
This may require some customization, and add or remove procedures to to the three I excluded from the list.
Now, in your sheets(1) worksheet code module, you'll want something like the following to initiate the macro you select from your drop down list:<pre>
Private Sub ComboBox1_Change()
Application.EnableEvents = False
Application.Run Me.ComboBox1.Value
Application.EnableEvents = True
End Sub</pre>
This worked well for me in Xl 2000 under some limited testing. Hope this helps.
_________________
Cheers,<font size=+2><font color="red">
Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-10-09 12:47