+ Reply to Thread
Results 1 to 17 of 17

Run Workbook_Open for every sheet

  1. #1
    Forum Contributor
    Join Date
    07-28-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    283

    Question Run Workbook_Open for every sheet

    Hi there,

    How can I run the code below for every sheet in the workbook, instead of just Sheet1?

    Please Login or Register  to view this content.
    Thanks,

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Run Workbook_Open for every sheet

    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,309

    Re: Run Workbook_Open for every sheet

    Just note that you will have to select each sheet in order to select A1 on each sheet.
    Rory

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Run Workbook_Open for every sheet

    Quote Originally Posted by rorya View Post
    Just note that you will have to select each sheet in order to select A1 on each sheet.
    I don't think the OP reall cares about that. I started to delete that line of code since it is basically useless.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,309

    Re: Run Workbook_Open for every sheet

    Who knows? It wouldn't be the first time I've seen code to select A1 on every sheet at startup.

  6. #6
    Forum Contributor
    Join Date
    07-28-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    283

    Re: Run Workbook_Open for every sheet

    Hi @JLGWhiz

    The macro is not working for me.
    The debug highlights the .Range("a1").Select
    Is it because all sheets are password protect?

    Any idea how to fix it?

    Thanks,

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,601

    Re: Run Workbook_Open for every sheet

    Hi !

    You must first activate the worksheet before selecting any cell or just use Application.Goto (to see in VBA inner help)

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Run Workbook_Open for every sheet

    Quote Originally Posted by ricdamiani View Post
    Hi @JLGWhiz

    The macro is not working for me.
    The debug highlights the .Range("a1").Select
    Is it because all sheets are password protect?

    Any idea how to fix it?

    Thanks,
    In short Yes. Your .Protect comes before your .Select.
    By default all cells are locked, and the lock becomes effective as soon as the sheet is protected.

    Either put the .Select prior to the .Protect or add an instruction to set A1 to Locked = False

  9. #9
    Forum Contributor
    Join Date
    07-28-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    283

    Re: Run Workbook_Open for every sheet

    Hi Richard, I moved the "select" line to the top but I am still getting the error in the "select" line. What am I doing wrong?

    Please Login or Register  to view this content.

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Run Workbook_Open for every sheet

    Since you are protecting the worksheet, why even select A1? If you delete that line altogether the code should run OK.
    but if you insist on using it, then
    Please Login or Register  to view this content.
    Would do it. But you will get some flicker and flash as each sheet is selected. I would just delete the line of code.
    Last edited by JLGWhiz; 05-22-2019 at 08:56 AM.

  11. #11
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,435

    Re: Run Workbook_Open for every sheet

    Move it out of the workbook_open event and put it in a sheetactivate event

    Please Login or Register  to view this content.
    then always when you enter the sheet the cursor will be placed in A1.

  12. #12
    Forum Contributor
    Join Date
    07-28-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    283

    Re: Run Workbook_Open for every sheet

    It is necessary to select A1 just when the spreadsheet is opened.

    The reason is, when I open the spreadsheet, the first column appearing is column E, which is the unprotected column.
    I can drag all the way to the left and save it, but when I close and reopen the workbook, column E will be the first column appearing again.

    So the idea is to select cell A1 so the first column appearing when the worksheet is opened is column A.

    Hope that makes sense.

  13. #13
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Run Workbook_Open for every sheet

    All you have to do is press Ctrl + Home to put A1 at the upper left position on your screen. Still I don't know why your sheet would open to column E if you save it in another mode, unless you have a Sheet_Activate macro that is automatically positioning that column. Normally, the sheet opens to the last saved configuration.

  14. #14
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Run Workbook_Open for every sheet

    You're missing the

    sh.Activate

    instruction

  15. #15
    Forum Contributor
    Join Date
    07-28-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    283

    Re: Run Workbook_Open for every sheet

    Where should I put sh.Activate?

  16. #16
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,309

    Re: Run Workbook_Open for every sheet

    Please Login or Register  to view this content.

  17. #17
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Run Workbook_Open for every sheet

    Here is a modification to my original post. See if it works for you.

    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 05-22-2019 at 01:51 PM.

+ 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] Workbook_Open if Cell is blank load certain sheet
    By jharvey87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2018, 03:37 PM
  2. [SOLVED] save a sheet with code on workbook_open included
    By bulina2k in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2014, 04:17 PM
  3. Updating add-in sheet on startup (workbook_open)
    By Bishonen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2013, 06:29 AM
  4. Private sub workbook_open code running workbook_open code from other project
    By marshall_massive in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2013, 06:07 PM
  5. Calling Sheet functions to Sub Workbook_Open Event??
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-28-2011, 05:15 PM
  6. Workbook_Open need not be run if the sheet is already open
    By Visceral in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2009, 05:04 AM
  7. Updating workbook_open from workbook_open
    By tonywig in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-24-2007, 07:39 AM

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