Documents opened by a macro opening in design mode

[DAAAAAN]

Hi there, over the course of the last few months I've been running a document at work (under visual basic version 6.3) that allows people to open up a range of important documents with a set of Macros that are assigned to buttons and shortcut keys.
Each of the documents that are opened are password protected and opened in read only mode.
Once opened the documents have some text from the original document copied into a defined range of cells.
The aim of the document is to speed up access to all the documents that are linked up and to that the text is copied from the initial page to the output page.

I recently made a change to try and improve it further that has brought up the most bizarre of errors.

I tried to include the ReadOnly:=True variable in the open method so that the dialog did not show when opening documents.

When opening the document via the buttons the new document opens fine
When opening the document with a Ctrl+Letter command the document opens fine
When opening the document with a Ctrl+Shift+Letter command without the read only parameter the document opens fine

When using the Ctrl+Shift+Letter and the ReadOnly:=True variable the document opens in design mode and the macro fails to copy the text from 1 document to the other.

I have tried tinkering with all of the different variables that I can and this set of conditions is the only one under which this error occurs and it is also the most valuable set of conditions which is really quite typical.

The most obvious thing to do would be to move the shortcut keys from Ctrl+Shift+Letter to Ctrl+Letter however I need to link up 11 Documents and there are only 7 letters on the keyboard available after excel has finished with it, none of which have any meaning in relation to the documents that they refer to. I can't bind to numbers or punctuation either so I'm kind of stuck. I could quite easily revert to what I was doing before however I was attempting to improve it and by all rights it should have worked.

Does anyone have any ideas on how to resolve this?

An approximation of the code looks like this

Sub Macro
'

'
On Error GoTo ErrorHandler
Workbooks.Open Filename:= _
"openeddocument.xls", ReadOnly:=True
Windows("originaldocument.xls").Activate
Range("range1").Select
Selection.Copy
Windows("openeddocument.xls").Activate
Range("range2").Select
ActiveSheet.Paste
Range("range3").Select
Exit Sub
ErrorHandler:
End Sub

 

[WalkerEvans]

I can't see any major problems with your code. However, if I understand your situation correctly, creating a new Menu on the Command Bar and naming it something like "Macros" should solve your problem. All of your macros would then be sub-items on this menu. This completely eliminates the need for multiple methods of running your macros, which is not really a good practice, and it should allow the ReadOnly:=True to properly function every time.

There is the added benefit of eliminating the "not enough letters" problem and will also allow you to name the items in such a way that they have a clear relationship to the documents. Set this up in a template available to all your users and everyone will have access to the new menu.


----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long