Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding Worksheets (Excel 2000)

    Hallo everyone

    Can anybody tell me; is it possible to hide certain sheets in a workbook depending on a choice from a drop down box?

    Any guidance will be appreciated.

    Regards
    Regards
    Kobus

  2. #2
    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: Hiding Worksheets (Excel 2000)

    Yes you can read something from the combobox and run code to hide a sheet. Could you elaborate on what you have/want?

    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Worksheets (Excel 2000)

    Steve

    Thank you for the reply

    I attach a workbook to explain. In the workbook there are a few sheets with a "2" in the sheet name. On the General Info sheet is a drop down box with the name Order Value. If you choose the first choice all the sheets with a "2" in the name must be hidden. Any other choice should show all the sheets.

    Hope this helps.

    Regards
    Regards
    Kobus

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

    Re: Hiding Worksheets (Excel 2000)

    You have protected the General Info sheet (with a password) so it is impossible to select anything from any drop down box on this sheet. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Worksheets (Excel 2000)

    Sorry Hans

    I attached an open version.

    Regards
    Regards
    Kobus

  6. #6
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Worksheets (Excel 2000)

    Hans

    Your remark on unable to choose anything from the drop down box promps another question.

    Why does this happen, every body in my office who use the protected versions from any of my sheets are able to choose options from the drop down boxes.

    Are different versions from Excel not compatible? Or some other reason?

    Regards
    Regards
    Kobus

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

    Re: Hiding Worksheets (Excel 2000)

    I'm using Excel 2002. Apparently you haven't unlocked the cells with a dropdown list in the version you posted.

  8. #8
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Worksheets (Excel 2000)

    Hans

    Thank you, it works great. May I ask another question out of ignorance?

    I first pasted the code in a Module, which did not work.

    I then clicked on the the Sheet name on the VB sheet and pasted the code in the space opened on the right hand side. Is the the sheet module as mentioned by you? What is the difference between this and the modules that appear as a seperate list after the work sheet names?

    Greatfull if you can enlighten me?

    My dank aan jou, in my opinie een van die groot Excel meesters.

    Regards
    Regards
    Kobus

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

    Re: Hiding Worksheets (Excel 2000)

    Right-click the worksheet tab of the General Info sheet and select View Code from the popup menu.
    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh As Worksheet
    If Not Intersect(Target, Range("lcur")) Is Nothing Then
    Application.EnableEvents = False
    For Each wsh In ActiveWorkbook.Worksheets
    If wsh.Name Like "*2*" Then
    wsh.Visible = Not (Range("lcur") = "

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

    Re: Hiding Worksheets (Excel 2000)

    Each worksheet in a workbook has an associated code module. This can be opened by right-clicking the sheet tab in Excel and selecting View Code from the popup menu (I forgot the last detail in my previous reply, now added), or by double clicking the sheet in the Project Explorer in the Visual Basic Editor.
    The worksheet module can be used to write code for the worksheet-related events such as Worksheet_Change (occurs whenever the user changes the value of a cell), Worksheet_SelectionChange (occurs whenever the user moves to another cell), etc. Worksheet modules are not intended to be used for general code.
    The workbook as a whole also has an associated code module. This can be opened by double-clicking ThisWorkbook in the Project Explorer.
    The workbook module is intended for workbook-related events such as Workbook_Open (occurs each time the workbook is opened), Workbook_BeforePrint (occurs when the workbook is printed or print previewed), etc.
    Standard modules (the kind you create by selecting Insert | Module in the Visual Basic Editor) are intended for general functions and procedures.

  11. #11
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding Worksheets (Excel 2000)

    Hans

    Thank you for the lesson, this will help me a lot in the future. Now I understand why some things that I tried in the past did not work.

    Thank you again

    Regards
    Regards
    Kobus

Posting Permissions

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