Unhide a paticular sheet

[bekema]

I made a work book with many sheets, they are all hide, except the first one.
In this sheet one must select for one particular option (macro).
This will delete many sheets, only a few left.
In the next sheet that appear, you can choose with macro's which other sheets you want to see (unhide)
Because i don't know which option they choose in the first sheets, i want to make a macro that looks if a sheet exists and than unhide that.
I tried a if structure, but that didn't work.
Know somebody a way?

[xcelion]

Hi bekema,

You could use the following function to check whether the sheet already exists then followed by your if to hide or unhide the sheet



Function SheetExists(sname as String)
' Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else: SheetExists = False
End Function

[Steiner]

Or you can just use the lazy method by just trying to unhide the sheet and let Excel run into an error you then catch:



Sub UnhideSafe()
On Error GoTo err
ThisWorkbook.Sheets("bla").Visible = True
Exit Sub
err:
MsgBox "Oops, that sheet does not exist"
End Sub

[bekema]

Hoi,
Thanks for you reply
I must explain a little bit more.
In the beginning the file has at least 7 sheets: adm, bkh, bam, fam, smw, rtl, oas
One of them i choose. for example- rtl- this sheet remains, but still hidden ;
the other sheets are deleted i need them no more.
Now i want to unhidel the remain rtl sheet, so the macro must look if the sheet exist
and unhide it. but it could be one of the 7 sheets.
remain the rtl sheets it must unhide the rtl sheet
remain the bam sheet it must unhide the rtl sheet
etc. All by the same button (macro)
thanks in advanced

[Steiner]

After you delete, is there only 1 sheet left? Then you could just use ThisWorkbook.Sheets(1).Visible...

If there still exist more sheets after you delete some, how are you going to determine which one to show?

[bekema]

Hoi,

There are still more sheets, with the macro i want to make it must determine which of the seven left and than unhide

Tia

[Steiner]

So it should unhide all sheets left? Or are there more than those 7 and you just want to unhide all of those 7 that are still left and leave the others hidden?

[bekema]

Hallo,

There are more sheets left. The meaning is that one of the 7 sheets, that left after the others deleted become unhide
I tried this but it doesn't work



Sub opleiding1()
If TypeName(Sheets("oas")) = "Worksheet" Then
Sheets("oas").Visible = True
Sheets("oas").Select
Exit Sub
Else
If TypeName(Sheets("smw")) = "Worksheet" Then
Sheets("smw").Visible = True
Sheets("smw").Select
Exit Sub
Else
If TypeName(Sheets("adm")) = "Worksheet" Then
Sheets("adm").Visible = True
Sheets("adm").Select
Exit Sub
Else
If TypeName(Sheets("bam")) = "Worksheet" Then
Sheets("bam").Visible = True
Sheets("bam").Select
Exit Sub
Else
If TypeName(Sheets("bkh")) = "Worksheet" Then
Sheets("bkh").Visible = True
Sheets("bkh").Select
Exit Sub
Else
If TypeName(Sheets("fam")) = "Worksheet" Then
Sheets("fam").Visible = True
Sheets("fam").Select
Exit Sub
Else
If TypeName(Sheets("rtl")) = "Worksheet" Then
Sheets("rtl").Visible = True
Sheets("rtl").Select
Exit Sub
Else
Sheets("naw").Select
End If
End If
End If
End If
End If
End If
End If
End Sub


I tried also something else with on error goto, but it only works for the first two



Sub opleiding()
On Error GoTo opl1
If TypeName(Sheets("oas")) = "Worksheet" Then
Sheets("oas").Visible = True
Sheets("oas").Select
Exit Sub
End If
opl1:
On Error GoTo opl2
If TypeName(Sheets("smw")) = "Worksheet" Then
Sheets("smw").Visible = True
Sheets("smw").Select
Exit Sub
End If
opl2:
On Error GoTo opl3
If TypeName(Sheets("adm")) = "Worksheet" Then
Sheets("adm").Visible = True
Sheets("adm").Select
Exit Sub
End If
opl3:
On Error GoTo opl4
If TypeName(Sheets("bam")) = "Worksheet" Then
Sheets("bam").Visible = True
Sheets("bam").Select
Exit Sub
End If
opl4:
On Error GoTo opl5
If TypeName(Sheets("bkh")) = "Worksheet" Then
Sheets("bkh").Visible = True
Sheets("bkh").Select
Exit Sub
End If
opl5:
On Error GoTo opl6
If TypeName(Sheets("fam")) = "Worksheet" Then
Sheets("fam").Visible = True
Sheets("fam").Select
Exit Sub
End If
opl6:
On Error GoTo naar_naw
If TypeName(Sheets("rtl")) = "Worksheet" Then
Sheets("rtl").Visible = True
Sheets("rtl").Select
Exit Sub
End If
naar_naw:
Sheets("naw").Select
End Sub


Tia

[Steiner]

Maybe then this one will go into the right direction:


Option Explicit

Sub UnhideSafe()
Dim i%
Dim WsNames As Variant
On Error GoTo err
WsNames = Array("Tabelle2", "Tabelle3", "Tabelle4")
For i = 0 To UBound(WsNames)
Worksheets(WsNames(i)).Visible = True
Next i
Exit Sub
err:
Debug.Print i & " " & err.Description
Resume Next
End Sub




Insert the sheet names into the array WsNames. Any errors like Worksheet not found will be shown only inside the debug window (it's the Debug.Print), but you can also disable that.

[bekema]

Hallo mr. Steiner

Thank you very much, it works :friends:

[Steiner]

Hi Tia,


Hallo mr. Steiner

That surely sounds cool :cool:. But Steiner is just a nickname, so a mr. is not really necessary.

Glad the code worked. You can mark this thread as solved by opening the ThreadTools - Menu right at the top of this thread.

Daniel