+ Reply to Thread
Results 1 to 4 of 4

BeforeClose Event Help

Hybrid View

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2010
    Posts
    8

    BeforeClose Event Help

    I have a user interface in Excel where users will enter names and other information in text boxes and select options in combo boxes. I need for the information to be cleared upon closing even if the user selects save so the the next user who opens it will not see the previously entered information.

    Right now I'm using the 'Private Sub Workbook_BeforeClose(Cancel As Boolean)' event function like this:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    ActiveSheet.OLEObjects("txtName").Object.Value = ""
    ActiveSheet.OLEObjects("TextBox2").Object.Value = ""
    ActiveSheet.OLEObjects("cboRegion").Object.Value = ""
    ActiveSheet.OLEObjects("cboQuarter").Object.Value = ""
    ActiveSheet.OLEObjects("cboDistrict").Object.Value = ""
    
    End
    This works BUT when I reopen the document I can still see the previously filled out information until I select enable macros, which then clears the information.

    I thought using the BeforeClose Event would prevent this from happening .... Any suggestions for clearing more effectively before it reopens?
    Last edited by trecie13; 08-14-2013 at 02:17 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: BeforeClose Event Help

    How about the Open enabled event?


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim S As String: S=ActiveSheet.Parent.Name
    
    ActiveSheet.OLEObjects("txtName").Object.Value = ""
    ActiveSheet.OLEObjects("TextBox2").Object.Value = ""
    ActiveSheet.OLEObjects("cboRegion").Object.Value = ""
    ActiveSheet.OLEObjects("cboQuarter").Object.Value = ""
    ActiveSheet.OLEObjects("cboDistrict").Object.Value = ""
    
    ActiveSheet.Parent.SaveAs Filename:=S, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    End
    Last edited by xladept; 08-14-2013 at 03:16 PM. Reason: Additional suggestion
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    01-23-2013
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: BeforeClose Event Help

    It looks like that code only saves the document as Macro Enabled but doesn't solve the issue of clearing the contents before the next user reopens it.

  4. #4
    Registered User
    Join Date
    01-23-2013
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: BeforeClose Event Help

    To give a better description of what''s going on ... I close the document just fine then I reopen it. When it is reopened I can still see all of the previously entered information that was there before it was closed last time but then I click the enable macros button that appears below my excel ribbon and all the information is cleared.

    The issue is that this defeats the purpose of clearing before close because the next user is still allowed to see all the information.

    Why would a BeforeClose event only run when the document is reopened and macros have been enabled?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with code for userform text boxes, combo boxes and excel
    By innerise in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2014, 09:07 AM
  2. I need a code for presence check to check multiple text boxes and combo boxes
    By Lee_wwfc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2013, 01:53 PM
  3. Combo Boxes / Text Boxes Not Updating
    By ckk403 in forum Excel General
    Replies: 4
    Last Post: 01-09-2012, 08:22 AM
  4. assigning data to lists boxes and combo boxes in userforms
    By weston.roberts in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2011, 08:46 PM
  5. I want to reference closed workbooks with combo boxes
    By Akira5284 in forum Excel General
    Replies: 1
    Last Post: 06-02-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1