Macro for hidding and unhidding sheets

[mjorgematos]

Hello guys,

I'm a newbie in terms of excel macros, and I would like to ask for your help.

I created a workbook with a few sheets and they are all hidden except sheet1 that has a list with the names of the other sheets.

The purpose is that, by choosing (with, for instance, "S"), the chosen sheet unhides.

Also I need to make the use of macros mandatory. Is it possible?

I attach an workbook with the example of what I need.

Thanks in advance for the help.

[Bob Phillips]

The VBA is password protected.

[mjorgematos]

Sorry, I forgot. I attach another workbook with no password.

[Bob Phillips]

Try this

[Larry Dunn]

Here's another approach that responds only to the letter S.

It also allows the user to put a set of S values in column B, for example,
then paste the whole set into column B at once. Or erase the whole
set at once.

Private Sub Worksheet_Change(ByVal Target As Range)
'Recognize 'S' in column B; hide/unhide sheet
Dim sSheetName As String
Dim oCell As Object
If Target.Column <> 2 Then Exit Sub
If Target.Row < 4 Then Exit Sub
For Each oCell In Selection
sSheetName = ActiveSheet.Range("A" & oCell.Row)
If sSheetName = "" Then GoTo DoNext
If UCase(oCell.Value) = "S" Then GoTo HideIt
Sheets(sSheetName).Visible = True
GoTo DoNext
HideIt:
Sheets(sSheetName).Visible = False
DoNext:
Next oCell
EndOfSubroutine:
End Sub

[mjorgematos]

Thanks guys, i'll try it and then i'll post the results

[Bob Phillips]

Here's another approach that responds only to the letter S.

It also allows the user to put a set of S values in column B, for example,
then paste the whole set into column B at once. Or erase the whole
set at once.

Private Sub Worksheet_Change(ByVal Target As Range)
'Recognize 'S' in column B; hide/unhide sheet
Dim sSheetName As String
Dim oCell As Object
If Target.Column <> 2 Then Exit Sub
If Target.Row < 4 Then Exit Sub
For Each oCell In Selection
sSheetName = ActiveSheet.Range("A" & oCell.Row)
If sSheetName = "" Then GoTo DoNext
If UCase(oCell.Value) = "S" Then GoTo HideIt
Sheets(sSheetName).Visible = True
GoTo DoNext
HideIt:
Sheets(sSheetName).Visible = False
DoNext:
Next oCell
EndOfSubroutine:
End Sub

Larry,

it can all be done without the nasty Gotos :)

Private Sub Worksheet_Change(ByVal Target As Range)
'Recognize 'S' in column B; hide/unhide sheet
Dim sSheetName As String
Dim oCell As Object
If Target.Column <> 2 Or Target.Row < 4 Then Exit Sub
For Each oCell In Target
sSheetName = Me.Range("A" & oCell.Row)
If sSheetName <> "" Then
Sheets(sSheetName).Visible = UCase(oCell.Value) <> "S"
End If
Next oCell
End Sub

[Larry Dunn]

I'm attaching a new workbook that gives a new capability.

The user can hide/unhide multiple tabs as a group.

[mjorgematos]

Try this

The workbook works fine. However when I tried to put the macro on my file I can't seem to put it to work.
Could you help me??
I attach the file that I'm working.
I would ask you for another thing: in your example we can choose all the worksheets at the same time; if possible I need that if I choose "S" for one of the sheets I can't choose "S" on the other sheets. Is it possible?

Thanks in advance

[Larry Dunn]

Jorge,

The code will not execute for you because you have it in a module. It needs to appear in the sheet called Inicio. In the VBA editor click on Inicio, then, up where it says 'General' select Worksheet. Then paste in your code.

[Larry Dunn]

Jorge,

Here's a sheet that does what you want, if I understood correctly.

Please note that it uses my code, not that of xld.

Our styles are very different.

Mine is sloppy, but I believe it's usually more readable.

Larry

[Bob Phillips]

The workbook works fine. However when I tried to put the macro on my file I can't seem to put it to work.
Could you help me??
I attach the file that I'm working.
I would ask you for another thing: in your example we can choose all the worksheets at the same time; if possible I need that if I choose "S" for one of the sheets I can't choose "S" on the other sheets. Is it possible?

Thanks in advance

[mjorgematos]

Thank you for your work.

However it seems that there is a small problem: I'm not able to choose another map - when trying to choose N for Mapa1 it gives the error that "One and only one S is allowed". Could you please see what is happening.

Thanks

[mjorgematos]

Thank you for your work.

However it seems that there is a small problem: I'm not able to choose another map - when trying to choose N for Mapa1 it gives the error that "One and only one S is allowed". Could you please see what is happening.

Thanks

[Bob Phillips]

Good point! Try this version.