Supress 'Select Sheet' dialog

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
I was looking for a simple way to check if a sheet existed in a wb without opening the wb.

I tried the following:
<code>
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear
</code>
This brings up a "Select Sheets" dialog box if the input sheet does not exist.
I'd like the code to skip the dialog box so it creates a REF# error in cell, then code can continue knowing that sheet is invalid w/o a manual response to the dialog box. If final form the code will supply various sheet names to be tested and the path will be changing.

I've been researching and trying different methods but I've not hit on the right one yet. I'm trying to avoid a long ADO code to check if sheet exist in closed wb. Any suggestions?
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
Application.DisplayAlerts = False '<==============this will suppress it
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear
 

TTom

Well-known Member
Joined
Jan 19, 2005
Messages
518
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
Application.DisplayAlerts = False '<==============this will suppress it
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear

That was the first thing I tried... no luck for this dialog box.
 

Rhadida

Board Regular
Joined
Nov 26, 2003
Messages
159
I had the same problem, but when looking around and trying various things I got my macro's to update links (with deleted sheets) to work using the following code at the start of the code:

Code:
On Error Resume Next
If Err = 0 Then Application.SendKeys ("{ESC}")

The ESC-key needs to be pressed when you get the "Select Sheet" pop-up and that will cancel the update for that particular link, since the sheet doesn't exist.

In your case your code would look like this:

Code:
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
If Err = 0 Then Application.SendKeys ("{ESC}")
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear
 
Last edited:

Rhadida

Board Regular
Joined
Nov 26, 2003
Messages
159
Hmmm it worked fine when first tested on the 1 file I used, but when I apply the code to a 2nd workbook. It doesn't, so sorry but I don't think the code will be helpfull to you.


I had the same problem, but when looking around and trying various things I got my macro's to update links (with deleted sheets) to work using the following code at the start of the code:

Code:
On Error Resume Next
If Err = 0 Then Application.SendKeys ("{ESC}")

The ESC-key needs to be pressed when you get the "Select Sheet" pop-up and that will cancel the update for that particular link, since the sheet doesn't exist.

In your case your code would look like this:

Code:
MySheet = InputBox("Enter Sheet Name")
MySheetPath = "='C:\MyFolder\[MyWb.xls]" & MySheet & "'!A1"
On Error Resume Next
If Err = 0 Then Application.SendKeys ("{ESC}")
ActiveCell.Formula = MySheetPath
Err.Clear
ActiveCell.Clear
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top