Drop Down boxes

[Igor]

Hi,

I am looking to create a drop down box with a difference. I have a set of macros, which are used as navigation tools within the spreadsheet. However, because their number is so big, the buttons I have assigned to them is taking up too much space. Is there a way I can create a drop down list of the macros, and once selected to have that macro run?

Would appreciate any help or guidance,

Thanks,

Igor

[Ivan F Moala]

On 2002-03-08 18:17, Igor wrote:
Hi,

I am looking to create a drop down box with a difference. I have a set of macros, which are used as navigation tools within the spreadsheet. However, because their number is so big, the buttons I have assigned to them is taking up too much space. Is there a way I can create a drop down list of the macros, and once selected to have that macro run?

Would appreciate any help or guidance,

Thanks,

Igor

You could try this;
'using ComboBox from Control toolbox = ActiveX object.
'Need to reference the VBA applications extensibility file
'in your project.

What it does:= Adds ALL macro names to the
combobox list when you click the dropbutton
When a selection is made it will ask you
if you want to run the routine......
you can skip this if you wish...BUT make sure
the Run routine is in the routine.


Private Sub ComboBox1_Click()
Dim Q As Integer

Q = MsgBox("Run " & ComboBox1.Text & " macro ??", vbYesNo)
If Q = vbYes Then Application.Run ComboBox1.Text

End Sub

Private Sub ComboBox1_DropButt*******()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim VBComp

ComboBox1.Clear

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
If VBComp.Type = vbext_ct_StdModule Then
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines

ComboBox1.AddItem .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Set VBCodeMod = Nothing
Next VBComp

End Sub


Ivan

[Igor]

Hi Ivan,

Thanks for the help, but it is still not working properly. When I select the drop down arrow, it has a problem with the second macro. It gives me the error message "compile error" User defined type not defined

on these lines

Private Sub ComboBox1_DropButt*******()
Dim VBCodeMod As CodeModule

Any idea what I am doing wrong?

many thanks,
Igor

[Ivan F Moala]

Hi Igor

'Need to reference the VBA applications extensibility file
'in your project.

In the VBA editor goto
Tools > references

Select the Microsoft Visual basics for applications Extensibility file


Ivan

OR via code;

Sub MakeLibrary()
On Error Resume Next ''if it already exits
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 0

End Sub


Ivan

[Igor]

Fantastic! It works great, thankyou very much!

[mdennis624]

Ivan,

I am importing data into Excel from a mainframe database. I distribute an .xls macro worksheet to users to load into their /XLStart folder. When they run the macro, it rearranges the raw data into a specific format. I want the main macro to also add a combobox that will list other macro functions that are available to the users as in the previous example. These other macros are Private Subs that are included in the worksheet that contains the main macro. I can get the main macro to add the combobox, but when the macro finishes, the combobox is empty. What am I doing wrong?
Thank you.

[Ivan F Moala]

On 2002-03-13 20:15, mdennis624 wrote:
Ivan,

I am importing data into Excel from a mainframe database. I distribute an .xls macro worksheet to users to load into their /XLStart folder. When they run the macro, it rearranges the raw data into a specific format. I want the main macro to also add a combobox that will list other macro functions that are available to the users as in the previous example. These other macros are Private Subs that are included in the worksheet that contains the main macro. I can get the main macro to add the combobox, but when the macro finishes, the combobox is empty. What am I doing wrong?
Thank you.

Change code to this.....

you may have to do case select to rid yourself
of the private code you don't want eg event procedures etc...

Private Sub ComboBox1_DropButt*******()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim VBComp

ComboBox1.Clear

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
If VBComp.Type = 1 Or VBComp.Type = 100 Then
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines

ComboBox1.AddItem .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Set VBCodeMod = Nothing
Next VBComp

End Sub


Ivan