Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Unanswered: Hiding sheets on event

    Ok, folks, this is probably a pretty dumb question, but I cannot figure out what I'm doing wrong. I'm new to VBA for excel, I'm more used to working in access and maybe the syntax is just screwing me up. I'm trying to create a module that will allow me to hide certain sheets in a workbook on open or close, in conjunction with button commands to unhide specific sheets depending on what reports users need to access. The button commands to hide and unhide sheets work fine, but my open and close procedures don't seem to be running at all. The code is as follows, any ideas?

    Private Sub Workbook_Open()
    Worksheets("sheet 2").Visible = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("sheet 2").Visible = False
    End Sub

    **the button functions are located between these two sub functions

  2. #2
    Join Date
    Jan 2012

    The Workbook_Open event is a procedure of the Workbook Object and as such, the Workbook_Open procedure MUST reside in the private module of the Workbook Object (ThisWorkbook).

    To achieve this while in VBE double click the module called ThisWorkbook. Here you can add the code you wish to run on open, for example

    Private Sub Workbook_Open()
    Worksheets("sheetname").Visible = False
    MsgBox ("sheetname is hidden")
    End Sub
    To get your on close event to run is a slightly more complex matter. Excel's before close event runs before a workbook is closed. However if you attempt to close a workbook that has not been saved your prompted with a message encouraging you to save. By the time this message appears the 'before close' event has already run. It is therefore possible to run the code for a workbook close but to keep the workbook open (by cancelling the prompt to save changes). One solution to this problem is...

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
    Msg = "Do you want to save the changes you made to "
    Msg = Msg & Me.Name & "?"
    Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
    Select Case Ans
    Case vbYes
    Case vbNo
    Me.Saved = True
    Case vbCancel
    Cancel = True
    Exit Sub
    End Select
    End If
    Worksheets("sheetname").Visible = True
    MsgBox ("sheetname is visible")
    End Sub
    Hope this helps,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts