Solved

Need Excel startup macro to restore settings

Posted on 2001-08-04
18
669 Views
Last Modified: 2007-11-27
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.
0
Comment
Question by:ksander
  • 8
  • 7
  • 3
18 Comments
 
LVL 2

Expert Comment

by:sergeTD
ID: 6351482
HI KSANDER,
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
0
 

Author Comment

by:ksander
ID: 6351515
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
0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6352018
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:ksander
ID: 6352106
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 ?
0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6352158
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
0
 

Author Comment

by:ksander
ID: 6352265
That didn't make any difference.

But this did:

At the start of the routine I had:
"Application.ScreenUpdating = False" (before the Worksheet loop)
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 ?

0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6352808
KSANDER,
You leave "Application.ScreenUpdating = False" but you must add: "Application.ScreenUpdating = True"  just before "End Sub" in order to refresh screen when you are leaving macro.
To finalize this matter, you must know something, these instructions are similars :
"Application.ScreenUpdating = False"
"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
0
 

Author Comment

by:ksander
ID: 6352901
Sorry I didn't make it clear, but, until I removed them, I have been using both "Application.ScreenUpdating = False"  and "Application.ScreenUpdating = True".

Are you saying I should start with "Application.Calculation = xlCalculateManual" and finish with "Application.ScreenUpdating = True" ?

0
 
LVL 2

Accepted Solution

by:
sergeTD earned 100 total points
ID: 6354101
I just want to prevent you from these freezing instructions which remain if the macro stops before the opposite instruction is executed.
So don't use any of them if it is not absolutely necessary.
Anyway, the trouble in one of your sheets is not due to them if the macro reach the line which command screen refresh.
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.
Serge TD.
0
 

Author Comment

by:ksander
ID: 6365119
I'd like to try using SendKeys to send  Ctrl + Home but can't figure out the syntax.  Does anybody know ?
0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6365712
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
0
 

Author Comment

by:ksander
ID: 6368421
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 = False
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 = True


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.  
0
 

Author Comment

by:ksander
ID: 6368439
I failed to say that if I replace:

Range("A1").Select  

with:

Application.TransitionNavigKeys = True
SendKeys "+{home}", True
Application.TransitionNavigKeys = False

the result is the same.
0
 

Expert Comment

by:amp072397
ID: 6709094
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
0
 

Author Comment

by:ksander
ID: 6710430
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.

0
 

Expert Comment

by:amp072397
ID: 6711369
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
0
 
LVL 2

Expert Comment

by:sergeTD
ID: 6712034
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
0
 

Expert Comment

by:amp072397
ID: 6712183
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
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question