Listbox for navigation

M

MaxRott

Ok, I have not been using excel or programmed VB in a while. I am trying
to create a workbook that will have about 40 items on one sheet
(sheet1), I want these items to be displayed in a list box on another
sheet (sheet2). Now for the tricky part, each of the 40 items will have
a sheet of data corresponding to it. I would like to create a method in
which the selection in the listbox will display the corresponding
sheet. Maybe keep a list box on every sheet of data so that I can
navigate freely between all 40 sheets based on my selection. Anyone
have any ideas? Any help is much appreciated.
 
B

Bob Phillips

Max,

Here's one way. Create a toolbar with the listbox on. This code assumes that
on Sheet3, column A has the Ids, column B has the worksheet names. Select
from the listbox, and that sheet is activated.

Private Sub Workbook_Open()
Dim i As Long

On Error Resume Next
Application.CommandBars("File List").Delete
On Error GoTo 0
With Application.CommandBars.Add(Name:=("File List"), _
temporary:=True)
.Controls.Add(Type:=msoControlDropdown).Caption = _
"DD Test"
With .Controls("DD Test")
.BeginGroup = True
For i = 1 To Worksheets("Sheet3").Cells(Rows.Count,
"A").End(xlUp).Row
.AddItem Worksheets("Sheet3").Cells(i, "A")
Next i
.OnAction = "ThisWorkbook.myMacro"
.ListIndex = 1
End With
.Visible = True
End With
End Sub

Sub myMacro()
With Application.CommandBars("File List").Controls("DD Test")
Worksheets(Worksheets("Sheet3").Cells(.ListIndex,
"B").Value).Activate
End With
End Sub


It goes in the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

MaxRott

thanks, I am a little confused though. How can I create a toolbar tha
relates to the listbox?

Ma
 
B

Bob Phillips

Max,

The code that I supplied will create that toolbar for you. Just align it to
your IDs, my code assumes Sheet3 column A.

Make sure you put it in the ThisWorkbook code module. To do this, hit
Alt-F11 from Excel, select the workbook, and double-click ThisWorkbook with
that workbook's project. Just put the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

ajw150

Hi,

Does anyone have/know where to get, and example of this working i
practice?

Andre
 
T

Tom Ogilvy

Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
Me.Listbox1.AddItem sh.name
Next



Private Sub Listbox1_Click()
worksheets(Me.Listbox1.Value).Select
End Sub
 
A

ajw150

Tom,

Thanks, seems simple, but cant get it to work! Where does it find th
sheet names? and the code doesnt like the Me part.

Andre
 
D

Dave Peterson

Where is your listbox and where is your code?

If it's on a userform, then the "Me" refers to the userform that holds the
listbox. And Tom's code would be somewhere behind that userform (maybe
userform_initialize).

If it's on a worksheet, then the "Me" refers to the worksheet that holds the
listbox (and code). This is not in a general module. (It might be in the
worksheet_activate event.)
 
Top