Disable "select sheet" prompt

  • Thread starter Thread starter jrfalck
  • Start date Start date
J

jrfalck

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
 
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/[email protected]

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.
 
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
 
Back
Top