change Sheets visiable?

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
if i close workbook, how can i take all sheet names and set visible false or true without sheet named "First".
like:
Private Sub Workbook_Open()
all = all sheetsname without sheet "First"
Worksheets(all).Visible = 1
Worksheets("First").Visible = 2
..
..
End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Monkey

Active Member
Joined
Feb 10, 2003
Messages
251
I Think You're looking for something like this,

Option Explicit
Sub Monkey()
Dim ConditionString As String ''Dutch For Sheet
Dim X As Integer
ConditionString = "First"
For X = 1 To Sheets.Count
If InStr(1, Sheets(X).Name, ConditionString) Then Sheets(X).Visible = 2 Else Sheets(X).Visible = 1
Next X
End Sub



Greetings,

Monkey
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi b,

How about something like this?
Code:
Sub Test()
    Dim ws As Worksheet
    Const strWSToKeep As String = "First"
    
    On Error Resume Next
    Set ws = Worksheets(strWSToKeep)
    On Error GoTo 0
    
    If Not ws Is Nothing Then
    'make sure the sheet to keep actually exists
        ws.Visible = xlSheetVisible
        'make sure it is visible
        For Each ws In ThisWorkbook.Worksheets
            If Not ws.Name = strWSToKeep Then ws.Visible = xlSheetHidden
        Next ws
        'hide all others
    End If
    
End Sub
HTH
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
ty all

but something not working
first code i try insert this way:
Code:
Private Sub Workbook_Open()
Dim ConditionString As String ''Dutch For Sheet
Dim X As Integer
ConditionString = "First"
For X = 1 To Sheets.Count
If InStr(1, Sheets(X).Name, ConditionString) Then Sheets(X).Visible = 2 Else Sheets(X).Visible = 1
Next X
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ConditionString As String ''Dutch For Sheet
Dim X As Integer
ConditionString = "First"
For X = 1 To Sheets.Count
If InStr(1, Sheets(X).Name, ConditionString) Then Sheets(X).Visible = 1 Else Sheets(X).Visible = 2
Next X
End Sub
this works only if i close first time my workbook.
if i reload my work, vba gives the following error:
Unable to set the Visible property of the Worksheet class

Richie code works for me ty :)
on open i only had to add the following code:
Worksheets("First").Visible = 2
but i also had to add
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,327
Messages
5,769,460
Members
425,550
Latest member
Bonebeast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top