Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
Is it possible to use a List Box within a spreadsheet (this would be on each
page) - that by selecting the first option in the list (e.g. Sheet 1, then Sheet 2, etc.) it would navigate to each sheet? Trying to put the finishing touches to a spreadsheet - navigating round the different sheets needs to be 'easier' for everyone - this way I can have a descriptive title within the List box for each area. As always your time, and expertise is greatly appreciated. Many Thanks, Al. ( ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
If you're using an ActiveX type listbox you can catch the Click event with
something like: Worksheets(lstErrors.ListIndex).Select Where lstErrors is the name of the listbox you're using -- Regards, Bill Lunney www.billlunney.com "MrAlMackay" wrote in message ... Is it possible to use a List Box within a spreadsheet (this would be on each page) - that by selecting the first option in the list (e.g. Sheet 1, then Sheet 2, etc.) it would navigate to each sheet? Trying to put the finishing touches to a spreadsheet - navigating round the different sheets needs to be 'easier' for everyone - this way I can have a descriptive title within the List box for each area. As always your time, and expertise is greatly appreciated. Many Thanks, Al. ( ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
Mr AL,
If you right-click on the arrows to the left of the sheet tab names, you get a sheet name list, effectively a listbox. Your users can select from there, and if you give the sheets descriptive names all solved without code or trickery. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "MrAlMackay" wrote in message ... Is it possible to use a List Box within a spreadsheet (this would be on each page) - that by selecting the first option in the list (e.g. Sheet 1, then Sheet 2, etc.) it would navigate to each sheet? Trying to put the finishing touches to a spreadsheet - navigating round the different sheets needs to be 'easier' for everyone - this way I can have a descriptive title within the List box for each area. As always your time, and expertise is greatly appreciated. Many Thanks, Al. ( ) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
How do I actually use this? Should this be part of a macro, or through VBA?
How do I know if it is an ActiveX listbox type? Thanks for your help on this. Al. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
OK, Application.goto seems to be adversely affected by being in the sheet
module even though the range is qualified. try it this way: Private Sub ListSheets_Click() On Error Resume Next Set sh = ThisWorkbook.Worksheets(Listsheets.Value) On Error GoTo 0 If Not sh Is Nothing Then sh.Activate sh.Range("A1").Select End If End Sub -- Regards, Tom Ogilvy Fede Querio wrote in message ... Tom, I'm afraid it didn't work....are you sure the code is ok? Federico "Tom Ogilvy" wrote in message ... If you add it from the control toolbox toolbar, then it is an activeX listbox. Right after you add it, then you will be in design mode. Double click on the listbox and you will be taken to the click event (if you are going to rename the listbox, do that first by right clicking on it an selecting properties. Then type in the new name. Let's assume your name it listSheets) Private Sub ListSheets_Click() On Error Resume Next set sh = thisWorkbook.worksheets(ListSheets.Value) On Error goto 0 if not sh is nothing then Application.goto sh.Range("A1") End sub You can also populate the listbox with the activate event in the same module. Private Sub Worksheet_Activate() Listsheets.clear for each sh in thisworkbook.Worksheets if sh.name < me.name then listsheets.additem sh.name end if Next End sub On the control toolbox toolbar, the upper left icon toggles design mode. You need to get out of design mode for the listbox code to work. Regards, Tom Ogilvy MrAlMackay wrote in message ... How do I actually use this? Should this be part of a macro, or through VBA? How do I know if it is an ActiveX listbox type? Thanks for your help on this. Al. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
Once I added a continuation character, your original code worked ok for me in my
special version of xl2002 <bg. Tom Ogilvy wrote: OK, Application.goto seems to be adversely affected by being in the sheet module even though the range is qualified. try it this way: Private Sub ListSheets_Click() On Error Resume Next Set sh = ThisWorkbook.Worksheets(Listsheets.Value) On Error GoTo 0 If Not sh Is Nothing Then sh.Activate sh.Range("A1").Select End If End Sub -- Regards, Tom Ogilvy Fede Querio wrote in message ... Tom, I'm afraid it didn't work....are you sure the code is ok? Federico "Tom Ogilvy" wrote in message ... If you add it from the control toolbox toolbar, then it is an activeX listbox. Right after you add it, then you will be in design mode. Double click on the listbox and you will be taken to the click event (if you are going to rename the listbox, do that first by right clicking on it an selecting properties. Then type in the new name. Let's assume your name it listSheets) Private Sub ListSheets_Click() On Error Resume Next set sh = thisWorkbook.worksheets(ListSheets.Value) On Error goto 0 if not sh is nothing then Application.goto sh.Range("A1") End sub You can also populate the listbox with the activate event in the same module. Private Sub Worksheet_Activate() Listsheets.clear for each sh in thisworkbook.Worksheets if sh.name < me.name then listsheets.additem sh.name end if Next End sub On the control toolbox toolbar, the upper left icon toggles design mode. You need to get out of design mode for the listbox code to work. Regards, Tom Ogilvy MrAlMackay wrote in message ... How do I actually use this? Should this be part of a macro, or through VBA? How do I know if it is an ActiveX listbox type? Thanks for your help on this. Al. -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use list box to navigate to different sheets...
It didn't for me in Excel 97 (and the syntax was correct)
Guess they must have fixed it. Also, can you spell word wrap. <g I didn't leave off the end if Regards, Tom Ogilvy Dave Peterson wrote in message ... Once I added a continuation character, your original code worked ok for me in my special version of xl2002 <bg. Tom Ogilvy wrote: OK, Application.goto seems to be adversely affected by being in the sheet module even though the range is qualified. try it this way: Private Sub ListSheets_Click() On Error Resume Next Set sh = ThisWorkbook.Worksheets(Listsheets.Value) On Error GoTo 0 If Not sh Is Nothing Then sh.Activate sh.Range("A1").Select End If End Sub -- Regards, Tom Ogilvy Fede Querio wrote in message ... Tom, I'm afraid it didn't work....are you sure the code is ok? Federico "Tom Ogilvy" wrote in message ... If you add it from the control toolbox toolbar, then it is an activeX listbox. Right after you add it, then you will be in design mode. Double click on the listbox and you will be taken to the click event (if you are going to rename the listbox, do that first by right clicking on it an selecting properties. Then type in the new name. Let's assume your name it listSheets) Private Sub ListSheets_Click() On Error Resume Next set sh = thisWorkbook.worksheets(ListSheets.Value) On Error goto 0 if not sh is nothing then Application.goto sh.Range("A1") End sub You can also populate the listbox with the activate event in the same module. Private Sub Worksheet_Activate() Listsheets.clear for each sh in thisworkbook.Worksheets if sh.name < me.name then listsheets.additem sh.name end if Next End sub On the control toolbox toolbar, the upper left icon toggles design mode. You need to get out of design mode for the listbox code to work. Regards, Tom Ogilvy MrAlMackay wrote in message ... How do I actually use this? Should this be part of a macro, or through VBA? How do I know if it is an ActiveX listbox type? Thanks for your help on this. Al. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using forms to navigate | Excel Discussion (Misc queries) | |||
List Sheets excluding sheets named ***-A | Excel Discussion (Misc queries) | |||
Is there a shortcut to view a list of all worksheets and navigate | Excel Discussion (Misc queries) | |||
Ctrl+Page Down to navigate sheets | Excel Discussion (Misc queries) | |||
Can I use a drop down list to navigate to other spreadsheets | Excel Worksheet Functions |