Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VBA execute on close (Excel 2000 SR-1)

    I am creating a workbook for a non-Excel user to use, and I wanted to turn off all features that are not necessary and could potentially foul things up. I have the following Workbook_Open procedure working, and want to reverse its function when the workbook is closed.

    <pre>Private Sub Workbook_Open()
    '
    ' MacCleanView Macro
    ' Turn off distractions
    '
    Application.CommandBars("Standard").Visible = False
    Application.CommandBars("Formatting").Visible = False
    With Application
    .DisplayFormulaBar = False
    .DisplayStatusBar = False
    End With
    End Sub</pre>


    Following is the closing code that I have tried, but I get an error message when it tries to execute. Any ideas what needs to be corrected?

    <pre>Private Sub Workbook_BeforeClose()
    '
    ' MacNormView Macro
    ' Restore distractions
    '
    Application.CommandBars("Standard").Visible = True
    Application.CommandBars("Formatting").Visible = True
    With Application
    .DisplayFormulaBar = True
    .DisplayStatusBar = True
    End With
    End Sub
    </pre>

    <img src=/w3timages/redline.gif width=33% height=2>
    A second question: Is it possible to set myself up a a "Trusted Source" for macro security?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA execute on close (Excel 2000 SR-1)

    David, the attached seems to be working for me. (I also hid the worksheet tabs and horizontal scrollbar for fun.) What error are you getting on open and on close? Did you drop the code into the workbook object general event level, or the the workbook object workbook event level; it makes a difference. (I have yet to learn why.)
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA execute on close (Excel 2000 SR-1)

    I'm really not sure which event level the code was placed in, but it is all working right now. Thanks <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I had copied part of the WorkbookOpen() code from another file, and just pasted it into the "This Workbook" window. When that bit of code was working as desired, I just copied it to the WorkbookBeforeClose() section and flipped the switches from False to True.

    I didn't think to record the error message, but the VBA help file was more confusing than helpful. Next time (and I'm sure there will be a next time) I'll be sure to jot it down for reference. <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

Posting Permissions

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