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.


---
Message posted from http://www.ExcelForum.com/

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)

"MaxRott" wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

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

Ma

--
Message posted from http://www.ExcelForum.com

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)

"MaxRott" wrote in message
...
thanks, I am a little confused though. How can I create a toolbar that
relates to the listbox?

Max


---
Message posted from http://www.ExcelForum.com/

Hi,

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

Andre

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy


"ajw150 " wrote in message
...
Hi,

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

Andrew


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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.)



"ajw150 <" wrote:

Tom,

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

Andrew

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson