Drop Down boxes
Drop Down boxes
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Drop Down boxes

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Fantastic! It works great, thankyou very much!

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    York, PA
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com