Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ListBox and Macros (2000)

    Hello y'all,

    I've Defined names at various cell locations in my Workbook. I've created macros that make the desired cell the active cell by going to that defined name. I have successfully assigned an individual macro to a CommandButton control.

    I want to build a navigational tool that will use these same macros in a ListBox control as a drop-down list. I have not yet been able to work out how to assign a macro to each of the different list items. Any suggestions? Should I be using a ListBox for this?

    Thanks much,
    Rich

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ListBox and Macros (2000)

    Why not use the built-in Edit | Go To dialog? Or, if you want more control, Jan Karel Pieterse's Name Manager?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox and Macros (2000)

    Thanks Hans for that lead.

    Still it begs the issue of assigning a macro to an item list in a ListBox.

    Thanks,
    Rich

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox and Macros (2000)

    You can't assign a macro to each item in a list box. What you would have to do is use one of the listbox's event routines (probably the Change Event) to look at which item is selected. Then use a Select Case statement to pick which name to GoTo.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ListBox and Macros (2000)

    You could create a (sub)menu, and add individual menu items to it, each with its own OnAction macro. Or you can create a dropdown list with several items and only one OnAction macro for the entire list. In <post:=328,351>post 328,351</post:> you'll find an example of how to create a dropdown on a toolbar and how the OnAction macro for it works.

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ListBox and Macros (2000)

    > begs the issue of assigning a macro to an item list in a ListBox

    Hans's suggestion for a dropdown on the toolbar is the best idea, but here is a sample workbook that shows Hans's first suggestion of Application.GoTo and also the solution that you asked for: a listbox that calls macros. I just used Legare's idea of using the Change event, but I called the macros directly instead of using Select Case. Select Case is safer, but I wanted to show you Application.Run. Here is the Sheet code. HTH --Sam
    <pre>Option Explicit
    '
    Private Sub Worksheet_Activate()
    ' This event initializes the listboxes
    Dim n As Name
    ' Add each named range to the listbox, lstNames
    lstNames.Clear
    For Each n In ActiveWorkbook.Names
    lstNames.AddItem n.Name
    Next n
    ' Add each macro to the listbox, lstMacros
    With lstMacros
    .Clear
    .AddItem "Macro1"
    .AddItem "Macro2"
    .AddItem "Macro3"
    End With
    End Sub
    '
    Private Sub lstNames_Change()
    ' This event goes to the first available cell below the selected named range
    If lstNames.Text = "" Then Exit Sub ' Clear generates a change event
    Application.Goto Reference:=lstNames.Text
    ' Select the first empty cell below the named range
    ActiveCell.End(xlDown).Offset(1, 0).Select
    End Sub
    '
    Private Sub lstMacros_Change()
    ' This event calls the selected macro
    If lstMacros.Text = "" Then Exit Sub
    Application.Run lstMacros.Text
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ListBox and Macros (2000)

    I hate it when I attach a workbook that doesn't work. <img src=/S/drop.gif border=0 alt=drop width=23 height=23> I thought the open would fire the worksheet open event, but it doesn't, so I moved the initialize code to a module and call it on both the open and the activate to be safe. BTW, the previous workbook works, but you have to switch to Sheet2 then back to Sheet1 to initialize the listboxes. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox and Macros (2000)

    Dear Sam,

    Your solution worked perfectly. I modified the macros to use a ComboBox, because my client was really ooking for a drop-down menu navigation tool on the worksheet. I didn't address going to a different worksheet in the active workbook. That shouldn't be too difficult to achieve, though? I defined all the Names before I wrote the macros.

    Here's the code:

    Sub initComboBoxes(dummy As Variant)
    ' Called by Sheet1::Activate & Workbook open to initializes the Combobox named ComboBoxNav

    ' Add each macro to the combobox, ComboBoxNav
    With Worksheets("ComboNavTool").ComboBoxNav
    .Clear
    .AddItem "GoToPlace1"
    .AddItem "GoToPlace2"
    .AddItem "GoToPlace3"
    .AddItem "GoToPlace4"
    End With
    End Sub

    Sub GoToPlace1()
    ' Create all macros before all other VBA
    '
    ' GoToPlace1 Macro
    Application.Goto Reference:="Place1"
    End Sub

    Sub GoToPlace2()
    '
    ' GoToPlace2 Macro
    Application.Goto Reference:="Place2"
    End Sub

    Sub GoToPlace3()
    '
    ' GoToPlace3 Macro
    Application.Goto Reference:="Place3"
    End Sub
    Sub GoToPlace4()
    '
    ' GoToPlace3 Macro
    Application.Goto Reference:="Place4"
    End Sub

    This next macro was assigned to Form Button to reset the Print Area easily on the Workbook
    Sub SetPrintPlace4()
    '
    ' SetPrintPlace4 Macro
    '
    ActiveSheet.PageSetup.PrintArea = ""
    ' Application.Goto Reference:="Place4"
    ActiveSheet.PageSetup.PrintArea = "$G$19:$M$36"
    End Sub


    I've attach the sample workbook, as well.

    Thanks for all your help.
    Rich

Posting Permissions

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