Replies

Thanks to all for responding.
Ross

Hans,
Brilliant, as usual. I guess I did not try all the combinations.
Thank you ver much.

Jan,
I’m not sitting at the affected computer at the moment, but I’m sure you’re absolutely right about the setting.
Thank you also for pointing out the sloppy error handling. Your advice is much appreciated.

Steve,
I get the error message “Out of range” In other words, it can’t find the file – which of course it’s not supposed to find.
Yes, the code looks fine and works fine, but not on my computer!

Thanks again – makes perfect sense.

Thanks Hans. That should fix it.
When/if you ever get time, it would be interesting to find out how button 1 disables button 2 but button 2 leaves button 1 alone.

Hans,
Thank you. Always right on point.

Steve,
Thank you for taking the time to suggest the various options. The Modeless msg box is notably absent, so I take it you don’t see a better way to use it than I am.
With respect to option 1) There are a number of steps the code must complete before we get to checking the list, so re-starting will not work. Moreover, in principle, I am not happy with code that requires users to be “taught” anything; if the interface is not intuitive it needs improvement.
2) Yes, I could create a Userform, but that would seem to be a lot more work than my modeless msg box. There is no way to validate the input. I have no way of knowing which of the following names is correct: ABC Inc|ABC Inc.|ABC-Inc|
3) This gets closer to what I need, but does seem a bit clunkier than the modeless msg box.
Good ideas but, as you say, with the limited amount of information I provided, not ideal.
Thanks

Steve,
Let’s say we have a list of 10 names and the macro is going to summarize some data for each of the 10. I want the user to review the list and see if any names have to be added or subtracted, and to make sure they are all spelled exactly as they are in the data. The modeless msg remains on the screen while the user edits the list. Is there a better way?

Hans,
Do you ever sleep?
Sorry, what I meant was that when I modify my code to accommodate the Modeless message box I have to move my dim statements around because now I have two or more procedures. Maybe I’m too restrictive in the scope of my variables, but I only declare them before the procedure when they apply to more than one.

Thanks. Seems so obvious when you say it!

Hans,
As always, spot on. I had some other code in the Document_Open event, but setting not print for Drawing Objects was not.
Thanks again

This may be too obvious, but why not create a macro in a separate file and assign it to a button or menu or something to give the user easy access.
You can then set the attribute to ReadOnly before you open the target Workbook:
SetAttr “C:My DocumentsExpenses.xls”, vbReadOnly
Workbooks.Open Filename:=”C:My DocumentsExpenses.xls”
Include a line in the code to close the macro workbook as soon as it’s done its thing.
Any use?

Hans,
As usual you come through. I had assumed that if I added the date programmatically, it would be changed every time the document was opened, but the way you describe, the date is only added when a new document is created from the template.
Thanks

Hans,
No, Excel 2000 does not support this method, so I can’t see a way to set things up in workbook or sheet events.
The first thing I was looking for, was the ID for the language I wanted. As is often the case, it was much easier simply to use the macro recorder while changing the language to what I wanted and then look at the code to get the ID, than to search the KB and TechNet where I found nothing.
That done, I find that I can run the spell checker for my preferred language:
Cells(19, 2).CheckSpelling CustomDictionary:=”CUSTOM.DIC”, IgnoreUppercase:=False _
, AlwaysSuggest:=True, SpellLang:=3084
The next problem is to turn the language back to English. This is done by invoking the spell checker a second time:
Cells(19, 2).CheckSpelling CustomDictionary:=”CUSTOM.DIC”, IgnoreUppercase:=False _
, AlwaysSuggest:=True, SpellLang:=1033
It means the user has to be aware of this, and when they see the change of language at the bottom of the dialog box, to click Cancel. It happens too fast for the user to notice that the spell checker has actually started again. Not perfect, but workable.
A related issue in my case, is that when I restrict the spell check to a specific range of cells, it won’t work if the cells are merged. So my procedure un-merges the cells and re-merges them when the user is finished spelling. This works, but introduces a further irritant: the word being checked is isolated and none of the rest of the text can be seen, denying the user the context. Maybe they can get used to this too???