Change code to open single page instead of all pages

0

Hi there,

I found your code to force macros to be enabled however I need it to open a single sheet instead of all sheets. How would I achieve that.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Runs after the user goes to close Excel but before any "Save" dialog
' may appear.
'
' Created by: TeachExcel.com
'

' Helper to show you when it runs.
'MsgBox "Close"

' Make the macro run faster by disabling screen updating
Application.ScreenUpdating = False
' Make sure no other events like this one run while we are working here
Application.EnableEvents = False

' Check if changes were made since the worksheets were made visible.
' Store the result into a variable.
' One line IF statement
If ThisWorkbook.Saved = True Then wbSaved = True

' Hide the worksheets and show the welcome screen.
Call Hide_Sheets

' Check if the workbook was saved before the sheets were hidden.
' This value is stored in the wbSaved variable before the worksheets
' are hidden because the process of hiding the worksheets will cause
' Excel to say that changes were made, even though the user himself
' didn't make any changes - all changed were as a result of hiding
' the worksheets and we don't want the user to have to see the Save
' dialog for this since it is not an expected behavior.
' (This can be confusing, I know!)
If wbSaved = True Then

    ' Save the workbook now since sheets were just hidden and we don't want the user
    ' to see them the next time they open this workbook unless they have macros enabled.
    ThisWorkbook.Save

End If

' Enable screen updating so Excel works like normal.
Application.ScreenUpdating = True
' Enable events again so everything works as expected
Application.EnableEvents = True

End Sub

Private Sub Workbook_Open()
' Runs when the workbook is opened or immediately after macros are enabled for the first time
' after the workbook is opened.
'
' Created by: TeachExcel.com
'

' Helper to show you when it runs.
'MsgBox "Open"

' Make the macro run faster by disabling screen updating
Application.ScreenUpdating = False
' Make sure no other events like this one run while we are working here
Application.EnableEvents = False

' Call the macro to show the worksheets
Call Show_Sheets

' Enable screen updating so Excel works like normal.
Application.ScreenUpdating = True
' Enable events again so everything works as expected
Application.EnableEvents = True

End Sub
Sub Show_Sheets()
' Show all worksheets except the splash screen
' Show worksheets before hiding the splash screen.
' - must always have at least 1 worksheet visible at any time
'
' Created by: TeachExcel.com
'

' Loop through all worksheets
For Each ws In Sheets

    ' Check if this is the welcome screen or not
    If ws.Name <> "Welcome" Then

        ' Show the worksheet
        ws.Visible = xlSheetVisible

    End If

Next ws

' Hide the welcome screen
Sheets("Welcome").Visible = xlSheetVeryHidden

' Set the workbook as "saved" even though the changes were not saved.
' - the "changes" were to unhide the worksheets and hide the welcome screen.
' - this allows us to not show the user the "Save" dialog if he closes the
'   workbook without actually changing anything in the worksheet.
ActiveWorkbook.Saved = True

End Sub
Sub Hide_Sheets()
' Hide all worksheets except the splash screen
' Show the splash screen before hiding the other worksheets.
' - must always have at least 1 worksheet visible at any time
'
' Created by: TeachExcel.com
'

' Show the welcome screen
Sheets("Welcome").Visible = xlSheetVisible

' Loop through all worksheets
For Each ws In Sheets

    ' Check if this is the welcome screen or not
    If ws.Name <> "Welcome" Then

        ' Hide the worksheet
        ws.Visible = xlSheetVeryHidden

    End If

Next ws

End Sub

Answer
Discuss

Answers

0

The code you have seems like an overkill for what you are asking. Please try this procedure. Paste it to a standard code module, such as would hav a name like Module1 when you insert it.

Sub ShowHideSheet(ByVal SheetName As String, _
                  ByVal ShowType As XlSheetVisibility)

    On Error Resume Next
    Worksheets(SheetName).Visible = ShowType
End Sub

The procedure below shows how to call the sub. You can paste it in the same standard code module. You can make more procedures like this one, giving them appropriate names, or you can use the single line of code each one contains within your bigger projects.

Sub ShowSheet2()
    ShowHideSheet "Sheet2", xlSheetVisible
End Sub

This sub specifies the sheet name and the variable ShowStatus. As you start to type this argument a dropdown will appear (if Intelisense is enabled in your VBE), listing the three admissible enumerations: xlSheetVisible, xlSheetHidden and xlSheetVeryHidden (in effect -1, 0 and 2). You can use their names or their values.

An error will occur if you try to hide the only visible shee in your workbook. On Error Resume Next will suppress the error but the procedure call will be without effect. Note that the code doesn't specify a workbook. Therefore it will take action on the ActiveWorkbook.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login