You are subscribed to this thread Changing reference between sheet

[jirkax]

Hi,
I have a serious problem. When I want to change reference between cells in two excel files. I set cell formula reference to a non existing name of sheet (='c:\apps\[Sesit11.xls]l5'!$A$1) (sheet l5 doesn't exist in file sesit11.xls). After change confirmation of cell formula, "Select sheet" dialog box opens (printscreen of dialog box on image).

My questions are:
Is it possible to disable cell refreshing after changes and keep cell changes without opening "select sheet" dialog box?
Is it possible to catch event, that opens "select sheet" dialog box?
Is it possible to click on "Cancel" button in opened dialog box?

Thank you for your ideas or solutions, that can help to solve my problem.

Regards Jiri Spacek

 

[SkipVought]

"Is it possible to disable cell refreshing after changes and keep cell changes without opening "select sheet" dialog box?"

"cell refreshing" Do you mean CALCULATION?

"Keep cell changes without opening "select sheet" dialog box" I do not understand what chnaging a value in a cell has with opening the select sheet dialog.

"Is it possible to catch event, that opens "select sheet" dialog box?"

Sheet events are listed in HELP and on the Sheet Code sheet, and this does not appear to be one of them.

"Is it possible to click on "Cancel" button in opened dialog box?"

Did you try it, and if so, you found out if it is possible.

What are you trying to accomplish here. You never did explain what YOUR PROBLEM is, you merely stated what happens under certain circumstances along with a series of questions.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jirkax]

So, my promles is:
I have more then 10000 xls files and I must change reference between them, because of moving this files to another disk location.
Because of count of files, I use rutine to change that references.
So i can't use clasic sheet code sheet to set up some simple event on each file.

Did you see the picture of dialog,which is in attachment?
After that, I hope, that you'll understeand my problem with changin references better.

"cell refreshing" Do you mean CALCULATION?
I tried to disable calculation, but nothing change.


"Keep cell changes without opening "select sheet" dialog box" I do not understand what chnaging a value in a cell has with opening the select sheet dialog.
I want to say with this qustion that, when i change reference from "='c:\apps\[Sesit11.xls]l11'!$A$1"(sheet l11 exist in file sesit11.xls) to "='c:\apps\[Sesit11.xls]l5'!$A$1" (sheet l5 doesn't exist in file sesit11.xls), the dialog box "select sheet" open and i must choose one of avaible sheet in sesit11.xls or a calcel that dialog box manualy.

So if the change of refence in cell doesn't apply, the dialog box doesn't open. this is the best option, how to solve my problem, but how?

I hope, that my second description of my problem is more understandable.

In case, this is my ICQ 258255338.

Thanks a lot.

Jirka_x

 

[SkipVought]

"I tried to disable calculation, but nothing change."

What is or is not changing. you say that "nothing changed" but never explained WHAT you are observing, unless I am to ASSUME that you are referring to this sheet selection, in which case you ought to explicitly state that.

Is there a reason that you are using cell references to open 10,000 workbooks to a specific cell? I'd suggest, if you are moving files, checking out the Name Statement in VBA Help.

Renames a disk file, directory, or folder.

Syntax

Name oldpathname As newpathname

The Name statement syntax has these parts:

Part Description
oldpathname Required. String expression that specifies the existing file name and location — may include directory or folder, and drive.
newpathname Required. String expression that specifies the new file name and location — may include directory or folder, and drive. The file name specified by newpathname can't already exist.



Remarks

The Name statement renames a file and moves it to a different directory or folder, if necessary. Name can move a file across drives, but it can only rename an existing directory or folder when both newpathname and oldpathname are located on the same drive. Name cannot create a new file, directory, or folder.

Using Name on an open file produces an error. You must close an open file before renaming it. Name arguments cannot include multiple-character (*) and single-character (?) wildcards.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}