------ Register to get rid of these "In Post" ads! ------
Assuming your dropdown list is in A1 of "Index" sheet and the DV dropdown
contains a list of other worksheets.
Copy/paste these two events into the "Index" sheet module.
Code:
Private Sub Worksheet_Activate()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Index" Then
sht.Visible = xlVeryHidden
End If
Next sht
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Sheets(Target.Value)
.Visible = True
.Activate
End With
endit:
Application.EnableEvents = True
End Sub
Right-click on the tab and "View Code" to access the module.
Edit to suit........"A1" or "Index" may not be correct for you.
Alt + q to return to the Excel window.
Select a name from the dropdown list to hide all sheets except "Index" and
selected sheet.
To bring up another sheet, switch back to "Index"
Gord
On Sat, 20 Jun 2009 08:32:01 -0700, Michele
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/excel-new-users/108195-lock-worksheet-based-dropdown-selection-prior-worksheet.html#post388393
<Michele@discussions.microsoft.com> wrote:
>Yes!!
>
>That's it exactly...I would like only the selected sheet unhidden, based on
>the dropdown...stuff the rest in the closet :-)
>
>This is a workbook used by about 8 team members, all within my department.
>
>We have been working from 12 different workbooks (each contained 4
>worksheets that were identical, and the ABC123 xxx that was different) just
>due to the differences. By using formulas based off user input in specific
>cells, I have been able to combine all 12 workbooks into 1 workbook with the
>4 identical tabs and then the 4 different ABC123 xxx tabs.
>
>There are currently quality issues with work being completed in the
>incorrect workbook.
>
>The dropdown currently contains 4 selections, with the potential to expand
>to 6 if I incorporate for another 2 person team.
>
>As far as the users being trusted to enable the macros, part of the training
>on using the new workbook will be to stress the importance of doing so. They
>have been asking for something like this, so I do not think it will be an
>issue. It's a conscious decision on their part, and they will be accountable
>if the QA team were to see all tabs.
>
>This is why I posted in new users...I have some good working knowledge of
>formulas, but am basically a VBA virgin (that is I have recorded some macros
>and have been able to edit as needed).
>
>Thanks~
>Michele
>
>"Gord Dibben" wrote:
>
>> Sounds like you want sheets hidden with only selected sheet from the list
>> becoming unhidden or is there another purpose behind limiting the access to
>> sheets only from the hyperlinks?
>>
>> This would require VBA and some protection, which BTW is not that secure in
>> Excel. How secure do you want this to be?
>>
>> You could do away with the hyperlinks and just work directly off the
>> dropdown selection.
>>
>> How many sheets do you have listed in your dropdown?
>>
>> Would users be trusted to enable macros?
>>
>>
>> Gord
>>
>> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
>> <Michele@discussions.microsoft.com> wrote:
>>
>> >Hi Gord,
>> >
>> >The user selects a tab option from a list.
>> >Examples would be:
>> >
>> >AB123 Jones family
>> >AB123 Smith family
>> >AB123 Anderson family
>> >
>> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks
>> >to each of the 3 tabs, that are formatted to 'appear' based on the selection
>> >from the list.
>> >
>> >Ideally the user should use the hyperlink to access the correct tab in the
>> >workbook,but in real life...well we know what happens...they click the tab
>> >itself.
>> >
>> >Should they select AB123 Jones family, I do not want them to be able to
>> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
>> >
>> >I know I could conditionally format each worksheet to 'gray out' based on
>> >the initial selection, but am working within each with quite a bit of
>> >conditional formatting already, so I am trying to find another 'safety', such
>> >as a locked tab or a msgbox to re-direct.
>> >
>> >I am not very savvy with writing my own macro code, but am trying to learn.
>> >
>> >Thanks!
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> What is conditionally formatted hyperlink?
>> >>
>> >> How will Excel know if an incorrect selection has been made from the
>> >> dropdown?
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
>> >> <Michele@discussions.microsoft.com> wrote:
>> >>
>> >> >Hi,
>> >> >
>> >> >I am creating a workbook, where I have several worksheet that are labeled
>> >> >with the same first 7 characters "ab123 -".
>> >> >
>> >> >The user selects one of these sheets from a dropdown in the first worksheet.
>> >> >I provide a conditionally formatted hyperlink within worksheet A, based on
>> >> >their selection, but for quality control want an additional step that either
>> >> >locks an incorrect selection or presenting a msgbox to re-direct.
>> >> >
>> >> >Can this be done?
>> >> >
>> >> >Thanks
>> >> >Michele
>> >>
>> >>
>>
>>