ksander
asked on
Need Excel startup macro to restore settings
We have a macro that starts when the workbook opens and works through each sheet. It protects sheets, hides columns, and resets things that may have been altered by the user.
The one thing it doesn't do is to set each sheet so that when it is opened by the user it displays the preferred start position (upper-left of the sheet) instead of where it was last left.
We also use "Freeze Panes" and that causes two other issues.
1)Our preferred "freeze point" is E8. Users may have scrolled right at the last session and when the sheet is re-opened columns A-D and R-Z are displayed instead of A-P.
2) Users may have moved the panes during the last session and we want to reset when opening.
Understand that we aren't trying to restrict the users during a session, we just want to reset each sheet to the standard start position the next time the workbook is opened.
Any help would be appreciated.
The one thing it doesn't do is to set each sheet so that when it is opened by the user it displays the preferred start position (upper-left of the sheet) instead of where it was last left.
We also use "Freeze Panes" and that causes two other issues.
1)Our preferred "freeze point" is E8. Users may have scrolled right at the last session and when the sheet is re-opened columns A-D and R-Z are displayed instead of A-P.
2) Users may have moved the panes during the last session and we want to reset when opening.
Understand that we aren't trying to restrict the users during a session, we just want to reset each sheet to the standard start position the next time the workbook is opened.
Any help would be appreciated.
ASKER
I have that ...
For Each ws In Worksheets
ws.Activate
ActiveSheet.Unprotect (pw)
(other stuff)
Range("A1").Select
ws.Protect (pw)
Next ws
... but it doesn't work
For Each ws In Worksheets
ws.Activate
ActiveSheet.Unprotect (pw)
(other stuff)
Range("A1").Select
ws.Protect (pw)
Next ws
... but it doesn't work
KSANDER,
If you want the cursor position Home when you open your sheet, and then Free Panes at E8, use these lines:
ActiveWindow.FreezePanes = False
Range("A1").Select
Range("e8").Select
ActiveWindow.FreezePanes = True
Is it what you need or not?
If not, please explain more simply where is your problem.
Serge TD
If you want the cursor position Home when you open your sheet, and then Free Panes at E8, use these lines:
ActiveWindow.FreezePanes = False
Range("A1").Select
Range("e8").Select
ActiveWindow.FreezePanes = True
Is it what you need or not?
If not, please explain more simply where is your problem.
Serge TD
ASKER
I inserted your code exactly. It worked fine - except for one sheet.
I had closed and saved the workbook with V20 in the upper left of that sheet. After restarting, V20 was still upper left (not A1 as I wanted) and the freeze point was AR46 (not e8).
It seems to work correctly if I leave a sheet a small amount away from the upper left corner of the sheet but if I move away considerably, it doesn't return at all.
It doesn't just happen on one sheet.
Is this clear? Any ideas ?
I had closed and saved the workbook with V20 in the upper left of that sheet. After restarting, V20 was still upper left (not A1 as I wanted) and the freeze point was AR46 (not e8).
It seems to work correctly if I leave a sheet a small amount away from the upper left corner of the sheet but if I move away considerably, it doesn't return at all.
It doesn't just happen on one sheet.
Is this clear? Any ideas ?
If "range("A1").Select" doesn't work in any situation, replace it by the following line which is a keystroke instruction:
SendKeys "{home}", True
Note this instruction must not be executed in VBA Debug
mode(step by step), so use F5 rather than F8 if you launch your macro from the Vbasic Window.
Hope this helps.
Serge TD
SendKeys "{home}", True
Note this instruction must not be executed in VBA Debug
mode(step by step), so use F5 rather than F8 if you launch your macro from the Vbasic Window.
Hope this helps.
Serge TD
ASKER
That didn't make any difference.
But this did:
At the start of the routine I had:
"Application.ScreenUpdatin
After I took it out, everything works fine. Of course there is a lot of trash flashing across the screen.
It works whether I use "Range("A1").Select" or
"SendKeys "{home}", True"
I guess we can live with the flashing trash but would prefer not to. Any ideas ?
But this did:
At the start of the routine I had:
"Application.ScreenUpdatin
After I took it out, everything works fine. Of course there is a lot of trash flashing across the screen.
It works whether I use "Range("A1").Select" or
"SendKeys "{home}", True"
I guess we can live with the flashing trash but would prefer not to. Any ideas ?
KSANDER,
You leave "Application.ScreenUpdatin
To finalize this matter, you must know something, these instructions are similars :
"Application.ScreenUpdatin
"Application.Calculation = xlCalculateManual".
they are useful for saving time only for huge sheets or complex calculations but they are dangerous:
If macro doesn't go to the end for whatever reason and you forgot this point, sheets are freezed.
Hope this helps,
serge TD
You leave "Application.ScreenUpdatin
To finalize this matter, you must know something, these instructions are similars :
"Application.ScreenUpdatin
"Application.Calculation = xlCalculateManual".
they are useful for saving time only for huge sheets or complex calculations but they are dangerous:
If macro doesn't go to the end for whatever reason and you forgot this point, sheets are freezed.
Hope this helps,
serge TD
ASKER
Sorry I didn't make it clear, but, until I removed them, I have been using both "Application.ScreenUpdatin
Are you saying I should start with "Application.Calculation = xlCalculateManual" and finish with "Application.ScreenUpdatin
Are you saying I should start with "Application.Calculation = xlCalculateManual" and finish with "Application.ScreenUpdatin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'd like to try using SendKeys to send Ctrl + Home but can't figure out the syntax. Does anybody know ?
After so much mails, I grant my comments as a good answer,
I'm sure you'll agree.
To make effective the use of
"SendKeys "{home}", True" and others "Sendkey" instructions, you have to use LOTUS navigation keys:
->Menu TOOLS->Options->Tab TRANSITION->select "Transition Navigation KEYS".
Hope this helps.
Serge TD
I'm sure you'll agree.
To make effective the use of
"SendKeys "{home}", True" and others "Sendkey" instructions, you have to use LOTUS navigation keys:
->Menu TOOLS->Options->Tab TRANSITION->select "Transition Navigation KEYS".
Hope this helps.
Serge TD
ASKER
Thanks for the tip about Navigation keys, but the problem remains ? the macro doesn?t work in all situations.
Here is a simplified version that sometimes works and sometimes doesnt:
Application.ScreenUpdating
ActiveWindow.FreezePanes = False 'In case the panes have been mis-positioned
Range("A1").Select ' Attempt to position to top left
Range("F8").Select
ActiveWindow.FreezePanes = True
Application.ScreenUpdating
IF - at the start of the macro the panes are on and in the correct position, the macro works fine.
BUT ? If the panes have been turned off or repositioned outside of the original screen area, the macro puts the freeze point in an obscure area and does not return the screen to top left.
BUT ? If I remove the ScreenUpdating lines, the macro works fine in all situations.
I'm as tired of this as you are. But the problem isn't solved.
Here is a simplified version that sometimes works and sometimes doesnt:
Application.ScreenUpdating
ActiveWindow.FreezePanes = False 'In case the panes have been mis-positioned
Range("A1").Select ' Attempt to position to top left
Range("F8").Select
ActiveWindow.FreezePanes = True
Application.ScreenUpdating
IF - at the start of the macro the panes are on and in the correct position, the macro works fine.
BUT ? If the panes have been turned off or repositioned outside of the original screen area, the macro puts the freeze point in an obscure area and does not return the screen to top left.
BUT ? If I remove the ScreenUpdating lines, the macro works fine in all situations.
I'm as tired of this as you are. But the problem isn't solved.
ASKER
I failed to say that if I replace:
Range("A1").Select
with:
Application.TransitionNavi
SendKeys "+{home}", True
Application.TransitionNavi
the result is the same.
Range("A1").Select
with:
Application.TransitionNavi
SendKeys "+{home}", True
Application.TransitionNavi
the result is the same.
Points on this question will be awarded to sergeTD unless ksander can give viable objection within 7 days.
His/her comments have given you the answer you require, but it does not work on one sheet. This could be due to the code of the specific sheet's layout or the screenupdating line. Or a combination of all three. He's stated that he could help if you post your code. You've chosen to ignore his request.
Trying to be fair.
Thanks
amp
Community Support Moderator
His/her comments have given you the answer you require, but it does not work on one sheet. This could be due to the code of the specific sheet's layout or the screenupdating line. Or a combination of all three. He's stated that he could help if you post your code. You've chosen to ignore his request.
Trying to be fair.
Thanks
amp
Community Support Moderator
ASKER
Yes - please give the points to sergeTD. His comments helped me find the workaround that I finally used.
I don't see where he asked me to post my code though.
I don't see where he asked me to post my code though.
His comment on 8/5/01 stated "All these mails could be avoid if you display integraly your macro, so it's possible for experts to find eventually what could be responsable of your problem."
Anyway, ksander, thank you so much for responding; many never do.
I'll force-award to serge.
:)
amp
Community Support Moderator
Anyway, ksander, thank you so much for responding; many never do.
I'll force-award to serge.
:)
amp
Community Support Moderator
AMP,
Without your intervention, I'm afraid Ksander who has already rejeted my answer, after so many questions and answers ,will never accept to give any points for my help.
Although he has conceded several times to get results with my comments:
"I inserted your code exactly.It worked fine - except for one sheet..." "It seems to work correctly if I leave a sheet a small amount away from .....It doesn't just happen on one sheet.... " "It works whether I use "Range("A1").Select" or.."
Finally, he asked you to grant my answer instead of doing it himself: such persons must be evicted from this unselfish site, they take unfair advantage of experts help.
Thanks you for your intervention and please think about such sanctions.
Serge TD
Without your intervention, I'm afraid Ksander who has already rejeted my answer, after so many questions and answers ,will never accept to give any points for my help.
Although he has conceded several times to get results with my comments:
"I inserted your code exactly.It worked fine - except for one sheet..." "It seems to work correctly if I leave a sheet a small amount away from .....It doesn't just happen on one sheet.... " "It works whether I use "Range("A1").Select" or.."
Finally, he asked you to grant my answer instead of doing it himself: such persons must be evicted from this unselfish site, they take unfair advantage of experts help.
Thanks you for your intervention and please think about such sanctions.
Serge TD
sergeTD, To be sure, we don't let that kind of behavior continue, but I've seen other questions by ksander, and most are handled properly. I will admit that after joining and participating as an expert, I too had laxed in some questions for a few months. So that I understand when that happens. And, none of ksander's other questions make it appear that ksander is reluctant to award points. I believe this was what we call *a fluke*, an exception rather than the rule. I think, simply because of the way you asked it, that ksander didn't understand when you said "display integrally". To me it almost sounded like some instruction they should carry out instead of pasting here.
Everything's okay, serge. But keep me informed whenever you see anything at all you think I ought to see. Or if you see me behaving in what you think is an unfair manner.
Thanks.
amp
Community Support Moderator
amp@experts-exchange.com
Everything's okay, serge. But keep me informed whenever you see anything at all you think I ought to see. Or if you see me behaving in what you think is an unfair manner.
Thanks.
amp
Community Support Moderator
amp@experts-exchange.com
If I understand you well, you just need to get your cursor
at home(i.e cell A1) when you open your sheet.
You have to add the following line to your macro:
Range("A1").Select
Hope this helps
Serge TD