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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,809
Messages
6,144,607
Members
450,559
Latest member
kwenda farai

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