Drop Down boxes

Igor

New Member
Joined
Mar 7, 2002
Messages
16
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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_DropButtonClick()
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
 
Upvote 0
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_DropButtonClick()
Dim VBCodeMod As CodeModule

Any idea what I am doing wrong?

many thanks,
Igor
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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_DropButtonClick()
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top