LinkBack URL
About LinkBacks
Hi there,
How can I run the code below for every sheet in the workbook, instead of just Sheet1?
Thanks,
Please Login or Register to view this content.
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?
Thanks,Please Login or Register to view this content.
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!
Just note that you will have to select each sheet in order to select A1 on each sheet.
Rory
I don't think the OP reall cares about that. I started to delete that line of code since it is basically useless.Originally Posted by rorya
Who knows? It wouldn't be the first time I've seen code to select A1 on every sheet at startup.
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,
Hi !
You must first activate the worksheet before selecting any cell or just use Application.Goto (to see in VBA inner help) …
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
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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.
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
Would do it. But you will get some flicker and flash as each sheet is selected. I would just delete the line of code.Please Login or Register to view this content.
Last edited by JLGWhiz; 05-22-2019 at 08:56 AM.
Move it out of the workbook_open event and put it in a sheetactivate event
then always when you enter the sheet the cursor will be placed in A1.Please Login or Register to view this content.
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.
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.
You're missing the
sh.Activate
instruction
Where should I put sh.Activate?
Please Login or Register to view this content.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks