Lock worksheet, based on dropdown selection prior worksheet

[Michele]

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

 

[Gord Dibben]

What is conditionally formatted hyperlink?

How will Excel know if an incorrect selection has been made from the
dropdown?


Gord Dibben MS Excel MVP

 

[Michele]

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]

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

 

[Michele]

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]

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

 

[Michele]

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]

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

 

[Michele]

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]

Good to hear.


Gord

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