+ Reply to Thread
Results 1 to 9 of 9

Auto Open and Auto Close Macro

  1. 02-13-2013, 08:45 AM #1
    SaleenaKhan
    SaleenaKhan is offline
    Registered User
    Join Date
    02-05-2013
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    18

    Exclamation Auto Open and Auto Close Macro

    PHP Code: 
    [B]Sub auto_open()
    '
    '     auto_open Macro
    '
                        MsgBox ("Welcome to Sabz Cupcakes Workbook")
                        Sheets("MainMenu").Visible = False
                        Sheets("CustomerInput").Visible = False
                        Sheets("ExistingCustomer").Visible = False
                        Sheets("OrderInput1").Visible = False
                        Sheets("OrderInput2").Visible = False
                        Sheets("Basket").Visible = False
                        Sheets("Invoice").Visible = False
                        Sheets("CustomerHistory").Visible = False
                        Sheets("OrderHistory").Visible = False
                        Sheets("Summary").Visible = False
                        Sheets("Chart1").Visible = False
                        Sheets("Chart2").Visible = False
                        Sheets("Chart3").Visible = False
                        Sheets("RawData").Visible = False
                        
        Sheets("(Correct)").Select
        Range("G13,G15").Select
        Range("G15").Activate
        Selection.ClearContents
        Range("G13").Select
        Sheets("(Correct)").Select
        Range("G15").Select

    End Sub

    ___________________________________________________________________________________________

    Sub auto_close()
    '
    ' auto_close Macro

                        MsgBox ("Thank You for using Sabz Cupcakes Workbooks")
                        Sheets("(Correct)").Select
                        Range("G13,G15").Select
                        Range("G15").Activate
                        Selection.ClearContents
                        Range("G13").Select
                        Sheets("MainMenu").Visible = True
                        Sheets("CustomerInput").Visible = True
                        Sheets("ExistingCustomer").Visible = True
                        Sheets("OrderInput1").Visible = True
                        Sheets("OrderInput2").Visible = True
                        Sheets("Basket").Visible = True
                        Sheets("Invoice").Visible = True
                        Sheets("CustomerHistory").Visible = True
                        Sheets("OrderHistory").Visible = True
                        Sheets("Summary").Visible = True
                        Sheets("Chart1").Visible = True
                        Sheets("Chart2").Visible = True
                        Sheets("Chart3").Visible = True
                        Sheets("RawData").Visible = True

    End Sub[/B] 

    I am ever so sorry for Including two very lengthy Macros, however i did not know how else to go about this problem. Basically, when i close and re-open the workbook, 5 sheets do not appear, which are the - ExistingCustomer, Sumarry, Chart1, Chart2, Chart3. Can anyone tell me why this is and please provide me with a solution?
    Last edited by SaleenaKhan; 02-13-2013 at 10:14 AM.
    Reply With QuoteRegister To Reply
  2. 02-13-2013, 09:53 AM #2
    haripopuri
    haripopuri is offline
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Auto Open and Auto Close Macro

    You have to use your macro's for workbook open and workbook close events as mentioned below. Any other events will not run the macro automatically upon workbook open or close. Remember to keep the codes in ThisWorkbook code window.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 02-13-2013, 09:57 AM #3
    haripopuri
    haripopuri is offline
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Auto Open and Auto Close Macro

    Read the forum rules, your code should be in code tags. Please edit your post to include tags. If you question is answered please update the thread as "Solved"
    Reply With QuoteRegister To Reply
  4. 02-13-2013, 10:09 AM #4
    SaleenaKhan
    SaleenaKhan is offline
    Registered User
    Join Date
    02-05-2013
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Auto Open and Auto Close Macro

    Quote Originally Posted by haripopuri View Post
    You have to use your macro's for workbook open and workbook close events as mentioned below. Any other events will not run the macro automatically upon workbook open or close. Remember to keep the codes in ThisWorkbook code window.
    Please Login or Register  to view this content.
    I have done exactly but the 5 sheets are still hidden?
    Reply With QuoteRegister To Reply
  5. 02-13-2013, 11:03 AM #5
    haripopuri
    haripopuri is offline
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Auto Open and Auto Close Macro

    Please upload your workbook. Also in Workbook Open event, your macro is setting the visible property of many sheets to 'false' which actually hide the worksheets. Did you wish to do the same?
    Reply With QuoteRegister To Reply
  6. 02-13-2013, 11:15 AM #6
    SaleenaKhan
    SaleenaKhan is offline
    Registered User
    Join Date
    02-05-2013
    Location
    Cardiff
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Auto Open and Auto Close Macro

    Quote Originally Posted by haripopuri View Post
    Please upload your workbook. Also in Workbook Open event, your macro is setting the visible property of many sheets to 'false' which actually hide the worksheets. Did you wish to do the same?
    I am sorry I am unable to upload my Workbook as it is part of my coursework.
    The initial idea is that when an individual opens the workbook, they will be required to enter a password and username and once they have entered the correct password and username, they have access to all the sheets within that specific workbook. And when the individual closes the workbook, the sheets should be hidden again, so that the next time they open they will have to follow the same process as mentioned before.
    Reply With QuoteRegister To Reply
  7. 02-13-2013, 11:33 AM #7
    haripopuri
    haripopuri is offline
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Auto Open and Auto Close Macro

    Trust you have a code and userform for username and password. For hiding and showing of sheets, protecting the workbook with password, below code can be tweaked.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 02-13-2013, 02:49 PM #8
    HaHoBe
    HaHoBe is offline
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Auto Open and Auto Close Macro

    Hi, haripopuri,

    Any other events will not run the macro automatically upon workbook open or close.
    Putting the Auto macros in a standard module will run them on opening or closing - even Excel2010 is supporting the old form of pre97 macros.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you
    Reply With QuoteRegister To Reply
  9. 02-13-2013, 03:17 PM #9
    thameem127
    thameem127 is offline
    Forum Contributor thameem127's Avatar
    Join Date
    04-06-2012
    Location
    Jeddah,Saudi Arabia
    MS-Off Ver
    Excel 2003,Excel 2007
    Posts
    321

    Re: Auto Open and Auto Close Macro

    Replace below code and try

    Please Login or Register  to view this content.
    See green color code. That should be changes.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1