Excel Setting in Options

[Dan Wilson]

Good day. I am using Excel 2003 on Windows XP Pro. A few days ago I submitted a question to the board concerning the use of an option titled Precision as Displayed.

I received an answer from one of the board regulars very quickly. The answer however, led to more questions, some of which were answered by the same person, but not all of them.

May I post the unanswered question here. I would assume that when a flag in the Options menu is set, it will affect every file that is opened in Excel from that point on. Is this true?

If so, is there a way to cause a macro to run automatically whenever particular files get opened? This would allow me to use the Precision as Displayed function on selected workbooks and not on others. I would then have to have two macros, one for setting the flag and another for removing the flag setting.

I currently have over 100 workbooks that are associated with the operation of my store. Most of the workbooks could take advantage of the Precision as Displayed flag and allow me to remove all of the ROUND functions that have been put in my formulas to circumvent the floating point arithmetic problems.

Thank you for any response to this issue.
Danno...

 

[RalphA]

Dan, you can write your own macros very easily. Click on Tools, Macro, Record Macro. Once you assign a shortcut key (I recommend using an upper-case leter), say, Ctrl+O, the O being a reminder that you want an Option. Next, manually do all the steps to complete your choice of the Options menu, and, when done, click on Tools and click on Stop Recording. There!

Now, create a second macro, and assign the shortcut keys, say, Ctrl+N, for No option.

The above should set you up for what you want, at least, in a manual- selection manner. Let us know if this works for you.

 

[Dan Wilson]

Reply to Ralph

Good day Ralph,

Thanks for the quick response, but you didn't answer either one of my questions. I know how to write macros and have been doing so for years.

Thanks, Dan...

 

[RalphA]

And a nicer day to you, dan.

The answer to both your questions is yes.

As I am not into VBA, I cannot help you further with the first question.

However, your second question ends with "I would then have to have two macros, one for setting the flag and another for removing the flag setting." Since this does requires a decision, and not an automatic solution, this led me to believe that you would have to create the two macros I described, either manually, as I suggested, or by the use of VBA. In either case, you would then either use shortcut keys, as I suggested, or use buttons to run the macros. So, I thought I had, basically, addressed a solution that addressed the reason for your second question. Was I wrong?

 

[Dan Wilson]

Reply to Ralph

Good day again Ralph.

In rereading my first reply to you I can see that it might have been interpreted as being somewhat terse. That was not my intention. I try to keep my posts as short as possible. Perhaps using the emoticons might have helped.

I do appreciate your taking the time to respond to my reply. The first yes is the one that bothers me. If I turn on the Precision as Displayed flag, then I will have to edit over half of my workbooks to see the effect of that decision.

Thank you for taking the time to include the instructons on how to write the macros. I was hoping that there was a way to have Excel run a macro every time that a particular file is opened. If I elect to set the precision flag, then I will have to remember to run one of two macros before doing any work on the file.

Thanks again for your patience and your help. Danno...

 

[RalphA]

Good morning, Dan (it is 8 am in Texas):

I appreciate your consideration for my possible reaction to your first reply to me. I did sense your impatience with my detailed macro-writing description, when you have had that knowledge for some years! And, I apologize, but, I had no way of knowing, and my answer was the most direct I could think of.

My personal experience is that, no matter how hard I try, I have great difficulty in understanding exactly what is required in a short post, or in any oral or written request, and I have to go back and forth, three or more times, to understand. Other people have a perception of what is needed and can answer accuretly the first time, but, not me. This leads me to be very verbose in my writings, and to ask various questions before answeriing an oral request. Sorry, but, that's the way I am.

As to having an Excel workbook automatically execute a macro on opening it, yes, that can easily be done in VBA (not by me, though), and I believe it has been posted in MrExcel more than once.

For an example of what I believe you are looking for, using the
“Private Sub Workbook_Open()”, please go to:
http://www.mrexcel.com/board2/viewtopic.php?t=199671

I believe you will have to enter that sub in each particular workbook that you want it to act on, selectively. And, I hesitate to instruct you on how to proceed, as you probably are an old hand at it, already

Let us know if this helps you solve your problem.

 

[Darren Bartrup]

How about writing an add-in which checks whether a newly opened workbook should have precision set. Either check for the existance of a specific worksheet or value in a cell (If range A1 = "Precision As Displayed" then set the option), or against a list of workbook names held within the add-in.

With the add-in always installed it would check every workbook you open.

EDIT: Saying that though, I just created a new workbook, set the option and saved the file. I then created a new workbook and the option wasn't set anymore. So the answer to your question would be No, it's not true.