Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet (97 SR2)

    Please help. Is it possible to have under one tab in a workbook multiple sheets? <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Not that I know of, that would kind of defeat the purpose of the tabs. What are you trying to accomplish?
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Thank you for replying. It is like a file which has tabs for countries and when you click on a certain country tab inside that sheet you can access more sheets with town names.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet (97 SR2)

    The only way I can see doing this, is to create tabs for each country and town name.

    Then when you have a click on a tab, it would loop thru all the sheet names, hiding all but the countries and the cities in that country.

    I have attached a file with some code in the workbook sheet activate event.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Would it work to click on a country Tab, then have Command Buttons on each country sheet for the applicable Cities

    Example A sheet named USA would have Buttons: NYC, Seattle, SF etc
    The code behind each button would navigate the user either to a new Section of the USA sheet or make a new sheet visible, say, the Seattle sheet.

  6. #6
    New Lounger
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Hi GoCrush. Could you attach an example for me pleeeeeese. Many thanks <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  7. #7
    New Lounger
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Hi I am trying to get this to work but I am having trouble inserting my sheets and please tell me how it hides the other tabs as I need to retrieve them

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Summer

    Attached is an example workbook with 3 methods of Navigating around a workbook.
    Method 1 uses a Worksheet USA. On this sheet are three defined ranges: NYC, Seattle, and SF
    The command buttons will navigate to each of these areas. The buttons are kept visible by
    using selecting A4 then Windows>Freeze Panes
    Method 2 uses a Sheet (FRANCE) with buttons to navigate to separate sheets for each "City"
    See the code for "ShowFRANCE" to see how sheets are made visible or hidden.
    Method 3 uses hyperlinks. Type a word in a cell. Select the cell. click Insert>hyperlink>click the name
    of the sheet and enter the cell to select in that sheet.

    Hope this gets you started.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Worksheet (97 SR2)

    PRess Alt+F11 in Steve's workbook to activate the Visual Basic Editor. You'll see the code he wrote. By studying it you'll see how the sheets are hidden/unhidden, and you'll also see what you must do if you add other countries and cities.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet (97 SR2)

    To expand on what Hans mentioned and provide some details. The code is in the "thisWorkbook object". The things you must modify:
    Add a line like:
    Dim vCountryx

    where "x" is the countrynumber in your list. The example has 3 countries so it has a vCountry1, 2, and 3. Dim as many as needed.

    The lines:
    vCountries = Array("Country Name1", "Country name2", "Country name3")
    vCountry1 = Array("City1", "City2")
    vCountry2 = Array("City3")
    vCountry3 = Array("City4", "City5", "City6")

    Indicate you have 3 countries (named "Country name1", "Country name2", and "Country name3". Country1 (named "Country Name1" contains the cities named "City1" and "City2". Country2 (named "Country Name2" contains the city named "City3" and "City2". Country3 (named "Country Name3" contains the cities named "City4", "City5", and "City6".

    The line:
    vCountries = Array("Country Name1", "Country name2", "Country name3")

    should be modified to add the names of each of the countries (these are the tab names, and they must be exact, case is unimportant) and each of the lines of the form "vCountryx" must be modified to list the appropriate tabs for the cities in that country.

    When any sheet is selected, the code is run and what it does is to load the arrays listing the country names and the cities for each country. The first check (looks to see if there is a match of the sheet name in the country name array) is to see if it is a "country name". If the sheet selected is not a country name, the code does nothing else.

    If it is a country name, it checks which one it is, and sets the "city array" (vCities) accordingly to the array of cities for that country (add more cases as needed) if you have more countries.

    The code then loops thru each worksheet in the workbook, and checks to see if it is a country name, if it is a country name, it does nothing.

    If the sheet is not a country name, it checks to see if the sheet name has a match in the cities for that country. If it is not in the city list, the sheet is hidden and if it is found it is made visible.

    If you add new sheets without adding it the appropriate city list, the sheet will be hidden and not made visible: only sheets explicitly listed in the country array are always visible and only cities explicitly listed in city list for country are made visible. All other sheets are hidden. [You could create another list of sheets that are always visible, in addition to "country names" if desired.]

    Hope this helps.
    Steve

  11. #11
    New Lounger
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet (97 SR2)

    Thank you so much. You guys are little angels and have helped me very much. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •