ListBox and Macros (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Thanks Hans for that lead.

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

Thanks,
Rich

Reply | Quote

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 you’ll find an example of how to create a dropdown on a toolbar and how the OnAction macro for it works.

Reply | Quote

> 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

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

Reply | Quote

I hate it when I attach a workbook that doesn’t work. 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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote