Consulting

Results 1 to 10 of 10

Thread: Dynamic Sheets

  1. 08-27-2008, 07:10 AM #1
    thomas.szwed
    thomas.szwed is offline
    VBAX Contributor
    Joined
    Nov 2007
    Posts
    194
    Location

    Question Dynamic Sheets

    Hi,

    I require some code to hide and unhide certain sheets in my workbook all depedant on a cell dropdown value that is chosen by a user. Pls take a look at the picture attached. It shows the dropdown list (which contains the names of all the sheets in my workbook) and all the sheets displayed at the bottom.

    One sheet must always remain visible called 'INPUT'. This sheet contains the dropdown. So when you select the sheetname from the dropdown list it then displays this sheet along with 'INPUT'. If the user changes their selection then the sheet must change to reflect their decision....

    Can anyone help me with some code here?

    I have this so far but has not worked for me

    [vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Me.Range("K10")) Is Nothing Then

    Select Case Range("K10").Value

    Case "SELECT": Call SheetVisibility(True, False, False, False, False, False, False)
    Case "New Malden - Manager": Call SheetVisibility(True, True, False, False, False, False, False)
    Case "New Malden - Staff": Call SheetVisibility(True, False, True, False, True, True, True)
    Case "New Malden - Manager": Call SheetVisibility(True, True, False, False, False, False, False)
    Case "Sudbury Process": Call SheetVisibility(True, False, False, True, False, False, False)
    Case "Sudbury StaffMan": Call SheetVisibility(True, False, False, False, True, False, False)
    Case "Wisbech Process": Call SheetVisibility(True, False, False, False, False, True, False)
    Case "Wisbech Process": Call SheetVisibility(True, False, False, False, False, False, True)

    End Select

    End If
    End Sub


    Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, sh3 As Boolean, sh4 As Boolean,
    sh5 As Boolean, sh6 As Boolean, sh7 As Boolean)

    Application.ScreenUpdating = False
    Worksheets("INPUT").Visible = xlSheetVeryHidden
    Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
    Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
    Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
    Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
    Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
    Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
    If sh1 Then Worksheets("INPUT").Visible = xlSheetVisible
    If sh2 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
    If sh3 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
    If sh4 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
    If sh5 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
    If sh6 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
    If sh7 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
    Application.ScreenUpdating = True

    End Function
    [/vba]
    Many Thanks
    Reply With Quote Reply With Quote
  2. 08-27-2008, 08:17 AM #2
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    You don't say in what way it is not working, but perhaps this is what you want

    [vba]

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Not Intersect(Target, Me.Range("K10")) Is Nothing Then

    Select Case Range("K10").Value

    Case "SELECT": Call SheetVisibility(False, False, False, False, False, False)
    Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False)
    Case "New Malden - Staff": Call SheetVisibility(False, True, False, True, True, True)
    Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False)
    Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False)
    Case "Sudbury StaffMan": Call SheetVisibility(False, False, False, True, False, False)
    Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False)
    Case "Wisbech Process": Call SheetVisibility(False, False, False, False, False, True)

    End Select

    End If
    End Sub


    Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
    sh3 As Boolean, sh4 As Boolean, _
    sh5 As Boolean, sh6 As Boolean)

    Application.ScreenUpdating = False
    Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
    Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
    Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
    Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
    Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
    Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
    If sh1 hen Worksheets("NewMaldenMan").Visible = xlSheetVisible
    If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
    If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
    If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
    If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
    If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
    Application.ScreenUpdating = True

    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  3. 08-27-2008, 08:20 AM #3
    thomas.szwed
    thomas.szwed is offline
    VBAX Contributor
    Joined
    Nov 2007
    Posts
    194
    Location
    In your response here, if 'SELECT' was chosen in the dropdown would all sheets disappear? Because thats not what i want, i need the INPUT sheet to be working at all times......

    Thanks
    Reply With Quote Reply With Quote
  4. 08-27-2008, 08:42 AM #4
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Why would INPUT disapper, I took out the code that touches that sheet.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  5. 08-28-2008, 05:30 AM #5
    thomas.szwed
    thomas.szwed is offline
    VBAX Contributor
    Joined
    Nov 2007
    Posts
    194
    Location
    because it has FALSE x 6 in the first sub?
    Reply With Quote Reply With Quote
  6. 08-28-2008, 05:36 AM #6
    thomas.szwed
    thomas.szwed is offline
    VBAX Contributor
    Joined
    Nov 2007
    Posts
    194
    Location
    The problems i have come across using your suggested code are as follows.

    1. When you choose an option from the dropdown nothing happens. You have to click off and click on the cell a number of times to make the sheets change.

    2. When selecting option NewMaldenStaff, 4 sheets appear instead of just the NewMaldenStaff one........

    ???

    Any help would be great thanks....
    Reply With Quote Reply With Quote
  7. 08-28-2008, 05:41 AM #7
    thomas.szwed
    thomas.szwed is offline
    VBAX Contributor
    Joined
    Nov 2007
    Posts
    194
    Location
    Point 2 has now been solved but i dont think the ON Select function is working on the sub as it requires you to click on and off the sub several times for it to work......?
    Reply With Quote Reply With Quote
  8. 08-28-2008, 06:01 AM #8
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    Is there any reason for a double up in the Case Select for "New Malden - Manager" or is that just a typo?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  9. 09-03-2008, 06:09 AM #9
    thomas.szwed
    thomas.szwed is offline
    VBAX Contributor
    Joined
    Nov 2007
    Posts
    194
    Location
    Its just a typo i think........

    XLD - is there anyway the code below could change so that it is run under a button as opposed to clicking on a cell, as this is proving abit unprofessional.

    [VBA]Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("K10")) Is Nothing Then

    Select Case Range("K10").Value

    Case "SELECT": Call SheetVisibility(False, False, False, False, False, False, False, False, False)
    Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False, False, False, False)
    Case "New Malden - Staff": Call SheetVisibility(False, True, False, False, False, False, False, False, False)
    Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False, False, False, False)
    Case "Sudbury Staff/Man": Call SheetVisibility(False, False, False, True, False, False, False, False, False)
    Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False, False, False, False)
    Case "Wisbech Staff/Man": Call SheetVisibility(False, False, False, False, False, True, False, False, False)
    Case "Aintree Process": Call SheetVisibility(False, False, False, False, False, False, True, False, False)
    Case "Aintree Staff/Man": Call SheetVisibility(False, False, False, False, False, False, False, True, False)
    Case "Factory Grade 4+": Call SheetVisibility(False, False, False, False, False, False, False, False, True)
    End Select

    End If
    End Sub


    Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
    sh3 As Boolean, sh4 As Boolean, _
    sh5 As Boolean, sh6 As Boolean, _
    sh7 As Boolean, sh8 As Boolean, _
    sh9 As Boolean)

    Application.ScreenUpdating = False
    Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
    Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
    Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
    Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
    Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
    Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
    Worksheets("AintreeProcess").Visible = xlSheetVeryHidden
    Worksheets("AintreeStaffMan").Visible = xlSheetVeryHidden
    Worksheets("FactGrade4+").Visible = xlSheetVeryHidden
    If sh1 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
    If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
    If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
    If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
    If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
    If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
    If sh7 Then Worksheets("AintreeProcess").Visible = xlSheetVisible
    If sh8 Then Worksheets("AintreeStaffMan").Visible = xlSheetVisible
    If sh9 Then Worksheets("FactGrade4+").Visible = xlSheetVisible
    Application.ScreenUpdating = True

    End Function[/VBA]

    Thanks
    Reply With Quote Reply With Quote
  10. 09-03-2008, 06:25 AM #10
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Sure, assign the ProcessK10 procedure to your button

    [vba]

    Public Sub ProcessK10()

    Select Case Range("K10").Value

    Case "SELECT": Call SheetVisibility(False, False, False, False, False, False, False, False, False)
    Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False, False, False, False)
    Case "New Malden - Staff": Call SheetVisibility(False, True, False, False, False, False, False, False, False)
    Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False, False, False, False)
    Case "Sudbury Staff/Man": Call SheetVisibility(False, False, False, True, False, False, False, False, False)
    Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False, False, False, False)
    Case "Wisbech Staff/Man": Call SheetVisibility(False, False, False, False, False, True, False, False, False)
    Case "Aintree Process": Call SheetVisibility(False, False, False, False, False, False, True, False, False)
    Case "Aintree Staff/Man": Call SheetVisibility(False, False, False, False, False, False, False, True, False)
    Case "Factory Grade 4+": Call SheetVisibility(False, False, False, False, False, False, False, False, True)
    End Select
    End Sub


    Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
    sh3 As Boolean, sh4 As Boolean, _
    sh5 As Boolean, sh6 As Boolean, _
    sh7 As Boolean, sh8 As Boolean, _
    sh9 As Boolean)

    Application.ScreenUpdating = False
    Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
    Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
    Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
    Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
    Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
    Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
    Worksheets("AintreeProcess").Visible = xlSheetVeryHidden
    Worksheets("AintreeStaffMan").Visible = xlSheetVeryHidden
    Worksheets("FactGrade4+").Visible = xlSheetVeryHidden
    If sh1 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
    If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
    If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
    If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
    If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
    If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
    If sh7 Then Worksheets("AintreeProcess").Visible = xlSheetVisible
    If sh8 Then Worksheets("AintreeStaffMan").Visible = xlSheetVisible
    If sh9 Then Worksheets("FactGrade4+").Visible = xlSheetVisible
    Application.ScreenUpdating = True

    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules