I am working on a utility that loops through every cell and based o
some rules either leaves the formula with no changes, or performs som
modifications on it before putting in back. So far works fine excep
when it finds a direct reference to an external file. If the referenc
is correct, that is both file and sheet exists, then my process ru
well. BUT when it finds the workseet but the name of the sheet doesn'
exist, Excel popsup with a "select sheet" prompt. I don't want that t
happen.
I have disabled alerts and calculation to manual. The effect is th
same as pressing F2 on it and enter. Excel popsup and asks me to selec
a sheet. Since this interrupts my process I would like for Excel jus
to take the link as is, and place the #REF on the cell. This way I ca
loop through all cells non-stop and later deal with those formulas tha
are linking to non-existant sheets.
I had the same problem when the formula was linking to an non-existan
file; Excel would promot to select a file since the "file was no
found". I deviced a workaround this, but I can't find a workaround th
"select sheet" prompt

--
jrfalc
-----------------------------------------------------------------------
jrfalck's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31877

I didn't think it was a hard question. Nobody out there has an answer??

--
jrfalc
-----------------------------------------------------------------------
jrfalck's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31877

Peppering your code with:
Sendkeys "{esc}"
may work to dismiss that dialog.

maybe you could validate the new sheet before you attempt the change.

There was a long discussion just recently:

http://google.com/groups?threadm=Iva...rum-nospam.com

This seemed to work ok, but slightly irritating.

Option Explicit
Sub testme01()

Dim myNewFormula As String
Dim myCell As Range

With ActiveSheet
Set myCell = .Range("a1")
myNewFormula = Replace(myCell.Formula, "book10", "book99")

If IsError(.Evaluate(myNewFormula)) Then
Application.SendKeys "{ESC}"
End If
myCell.Formula = myNewFormula
End With

End Sub

Interestingly, in xl2002, I couldn't change the worksheet name to a sheet that
didn't exist. I'm not sure if that's new in xl2002 or if I did something weird.

I tried typing in the non-existent sheet name and it reverted back. I couldn't
do it in code. I couldn't do an Edit|replace|replace all.

I changed the workbook name in my sample code.

jrfalck wrote:

I didn't think it was a hard question. Nobody out there has an answer???

--
jrfalck
------------------------------------------------------------------------
jrfalck's Profile: http://www.excelforum.com/member.php...o&userid=16667
View this thread: http://www.excelforum.com/showthread...hreadid=318770

--

Dave Peterson

Thanks, it worked. It's just a little annoying since the replace formul
procedure was in one of the runs replacing formulas in more than 5
cells, all the flickering in the screen of "cancel" prompts bein
exceuted got to be a little annoying. But it works

--
jrfalc
-----------------------------------------------------------------------
jrfalck's Profile: http://www.excelforum.com/member.php...fo&userid=1666
View this thread: http://www.excelforum.com/showthread.php?threadid=31877