Solved

Need Excel startup macro to restore settings

Posted on 2001-08-04
18
656 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ksander
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:ksander
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now