Listbox for navigation
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/ |
Listbox for navigation
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/ |
Listbox for navigation
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 |
Listbox for navigation
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/ |
Listbox for navigation
Hi,
Does anyone have/know where to get, and example of this working i practice? Andre -- Message posted from http://www.ExcelForum.com |
Listbox for navigation
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/ |
Listbox for navigation
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 |
Listbox for navigation
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 |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com