------ Register to get rid of these "In Post" ads! ------


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

------ Register to get rid of these "In Post" ads! ------


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

------ Register to get rid of these "In Post" ads! ------


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
>
>

------ Register to get rid of these "In Post" ads! ------


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
>>
>>

------ Register to get rid of these "In Post" ads! ------


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
> >>
> >>
>
>

------ 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.

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
<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
>> >>
>> >>
>>
>>

------ Register to get rid of these "In Post" ads! ------


Dropdown was in E5 of "Index"...though I must not have done this correctly,
since it hid ALL of my worksheets, leaving only the last one...which btw was
very slick, even though not quite what I wanted :-) Perhaps I got a tad
confused because you mentioned A1 but the code below references Range "H1"?

There are worksheets that I need to remain visible (there are several that
are used in conjunction with the the ones I am trying to limit. I need
Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6,
Sheet7 or Sheet8 depending on the dropdown.

"Gord Dibben" wrote:

> 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.
>
> 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
> <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
> >> >>
> >> >>
> >>
> >>
>
>

------ Register to get rid of these "In Post" ads! ------


The "A1" was a typo.

You have an "Index" sheet and Sheet1 through 4 to remain visible at all
times?

Sheet5 through 8 will be hidden until selected from the dropdown?

User selects Sheet5 and it becomes visible. When user finished with Sheet5,
switches back to "Index" sheet to select another sheet.......Sheet5 becomes
hidden.

Is that basically what you need?

In the following code, edit Sheets(Array( sheetnames to suit.

Private Sub Worksheet_Activate()
Dim sht As Worksheet
For Each sht In Thisworkbook.Worksheets(Array _
("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
sht.Visible = xlVeryHidden
Next sht
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("E5")) 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

One more thing..............do you want Sheets 5 through 8 hidden when the
workbook opens?

You could add this to your Thisworkbook module

Private Sub Workbook_Open()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets(Array _
("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
sht.Visible = xlVeryHidden
Next sht
End Sub

I know I'm overloading you but use as much or as little as you want

If too confusing, send your workbook to my email.

gorddibbATshawDOTca

Replace the AT and DOT


Gord

On Sat, 20 Jun 2009 15:12:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>Dropdown was in E5 of "Index"...though I must not have done this correctly,
>since it hid ALL of my worksheets, leaving only the last one...which btw was
>very slick, even though not quite what I wanted :-) Perhaps I got a tad
>confused because you mentioned A1 but the code below references Range "H1"?
>
>There are worksheets that I need to remain visible (there are several that
>are used in conjunction with the the ones I am trying to limit. I need
>Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6,
>Sheet7 or Sheet8 depending on the dropdown.
>
>"Gord Dibben" wrote:
>
>> 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.
>>
>> 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
>> <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
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>

------ Register to get rid of these "In Post" ads! ------


SWEET!!!

I used both the "Index" and the "Thisworkbook" code below, and this gives me
99.9% of what I wanted :::does happy dance:::

I can train around the fact that while still in the workbook if they go back
to Index, it re-hides the selected sheet (they would never use Sheets 5-8 at
the same time, and they can just re-select on the dropdown) ~ what you have
helped me with is a H-U-G-E gain for my team.

And you explained things so that this newbie actually understands (albeit
dimmly) what the code is doing for me :-)

Thanks so much Gord ~
Michele

"Gord Dibben" wrote:

> The "A1" was a typo.
>
> You have an "Index" sheet and Sheet1 through 4 to remain visible at all
> times?
>
> Sheet5 through 8 will be hidden until selected from the dropdown?
>
> User selects Sheet5 and it becomes visible. When user finished with Sheet5,
> switches back to "Index" sheet to select another sheet.......Sheet5 becomes
> hidden.
>
> Is that basically what you need?
>
> In the following code, edit Sheets(Array( sheetnames to suit.
>
> Private Sub Worksheet_Activate()
> Dim sht As Worksheet
> For Each sht In Thisworkbook.Worksheets(Array _
> ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
> sht.Visible = xlVeryHidden
> Next sht
> End Sub
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Intersect(Target, Me.Range("E5")) 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
>
> One more thing..............do you want Sheets 5 through 8 hidden when the
> workbook opens?
>
> You could add this to your Thisworkbook module
>
> Private Sub Workbook_Open()
> Dim sht As Worksheet
> For Each sht In ThisWorkbook.Worksheets(Array _
> ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
> sht.Visible = xlVeryHidden
> Next sht
> End Sub
>
> I know I'm overloading you but use as much or as little as you want
>
> If too confusing, send your workbook to my email.
>
> gorddibbATshawDOTca
>
> Replace the AT and DOT
>
>
> Gord
>
> On Sat, 20 Jun 2009 15:12:01 -0700, Michele
> <Michele@discussions.microsoft.com> wrote:
>
> >Dropdown was in E5 of "Index"...though I must not have done this correctly,
> >since it hid ALL of my worksheets, leaving only the last one...which btw was
> >very slick, even though not quite what I wanted :-) Perhaps I got a tad
> >confused because you mentioned A1 but the code below references Range "H1"?
> >
> >There are worksheets that I need to remain visible (there are several that
> >are used in conjunction with the the ones I am trying to limit. I need
> >Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6,
> >Sheet7 or Sheet8 depending on the dropdown.
> >
> >"Gord Dibben" wrote:
> >
> >> 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.
> >>
> >> 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
> >> <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
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>

------ Register to get rid of these "In Post" ads! ------


Good to hear.


Gord

On Sun, 21 Jun 2009 11:53:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>SWEET!!!
>
>I used both the "Index" and the "Thisworkbook" code below, and this gives me
>99.9% of what I wanted :::does happy dance:::
>
>I can train around the fact that while still in the workbook if they go back
>to Index, it re-hides the selected sheet (they would never use Sheets 5-8 at
>the same time, and they can just re-select on the dropdown) ~ what you have
>helped me with is a H-U-G-E gain for my team.
>
>And you explained things so that this newbie actually understands (albeit
>dimmly) what the code is doing for me :-)
>
>Thanks so much Gord ~
>Michele
>
>"Gord Dibben" wrote:
>
>> The "A1" was a typo.
>>
>> You have an "Index" sheet and Sheet1 through 4 to remain visible at all
>> times?
>>
>> Sheet5 through 8 will be hidden until selected from the dropdown?
>>
>> User selects Sheet5 and it becomes visible. When user finished with Sheet5,
>> switches back to "Index" sheet to select another sheet.......Sheet5 becomes
>> hidden.
>>
>> Is that basically what you need?
>>
>> In the following code, edit Sheets(Array( sheetnames to suit.
>>
>> Private Sub Worksheet_Activate()
>> Dim sht As Worksheet
>> For Each sht In Thisworkbook.Worksheets(Array _
>> ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
>> sht.Visible = xlVeryHidden
>> Next sht
>> End Sub
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Intersect(Target, Me.Range("E5")) 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
>>
>> One more thing..............do you want Sheets 5 through 8 hidden when the
>> workbook opens?
>>
>> You could add this to your Thisworkbook module
>>
>> Private Sub Workbook_Open()
>> Dim sht As Worksheet
>> For Each sht In ThisWorkbook.Worksheets(Array _
>> ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
>> sht.Visible = xlVeryHidden
>> Next sht
>> End Sub
>>
>> I know I'm overloading you but use as much or as little as you want
>>
>> If too confusing, send your workbook to my email.
>>
>> gorddibbATshawDOTca
>>
>> Replace the AT and DOT
>>
>>
>> Gord
>>
>> On Sat, 20 Jun 2009 15:12:01 -0700, Michele
>> <Michele@discussions.microsoft.com> wrote:
>>
>> >Dropdown was in E5 of "Index"...though I must not have done this correctly,
>> >since it hid ALL of my worksheets, leaving only the last one...which btw was
>> >very slick, even though not quite what I wanted :-) Perhaps I got a tad
>> >confused because you mentioned A1 but the code below references Range "H1"?
>> >
>> >There are worksheets that I need to remain visible (there are several that
>> >are used in conjunction with the the ones I am trying to limit. I need
>> >Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6,
>> >Sheet7 or Sheet8 depending on the dropdown.
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> 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.
>> >>
>> >> 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
>> >> <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
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>