Temporarily Disable "Select Sheet" Dialog


Board Regular
Apr 5, 2005

I'm working on a workbook that will grab information from another workbook that a user selects. The only problem is that if a named range doesn't exist in the file they're opening the "Select Sheet" dialog opens up. I want to prevent this dialog from opening up at all! Ultimately, if the cell returns a #NAME? error, or any other error for that matter, I will just replace the returned information with "".

To be clear, the "Select Sheet" dialog box is the one that reads "Select the sheet to update values from".

...I know this leads to all sorts of questions but ultimately if I can just disable the "Select Sheet" dialog from opening up that would be great!


Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
from what I understand your pronlem

in the oode add a statement at a suitable place
on error goto line1
you introduce a line
line1:(with the colon)
in a place skipping the select sheet code statements

This on error statement comes before the select sheet statement.
I hope I made myself clear.
Upvote 0
Good idea... but ultimately that didn't seem to work. I've pasted my code below so someone might get a better idea of what I'm trying to do. Perhaps there is an altogether better solution; at this point, I'm open to ideas.

Sub OpenBudgetAndPasteStaticInformation()

'Named Ranges in Source Workbook
'  setupDivision
'  setupRegion
'  setupCommunity
'  setupDLO

Dim FolderName As String
Dim FileName As String
Dim ExcelWorkbookName As String
Dim ExcelWorksheetName As String
Dim ExcelFormula As String

Dim i As Integer
Dim Position As Integer
Dim FindChar As String

' Locate the file you want to retrieve information from
    FullFileName = Application.GetOpenFileName("Excel Files (*.xls;*.xlsx), *.xls; *.xlsx", , _
               "Select the Land Budget you wish to open and copy cash flow information from")
    If FullFileName = False Then
        MsgBox "No File Selected"
        Exit Sub
    End If

' Determine folder name, file name, and excel naming of this file

    FindChar = "\"
    For i = 1 To Len(FullFileName)
        If Mid(FullFileName, i, 1) = FindChar Then
            Position = i
        End If
    Next i

    FolderName = Left(FullFileName, Position)
    FileName = Right(FullFileName, Len(FullFileName) - Position)
    ExcelWorkbookName = "'" & FolderName & "[" & FileName & "]"
    ExcelWorksheetName = "Setup'!"
    ExcelFormula = ExcelWorkbookName & ExcelWorksheetName & "setupDivision"
    MsgBox "Full File Name: " & FullFileName & vbNewLine & vbNewLine & _
       "Folder Path: " & FolderName & vbNewLine & vbNewLine & _
       "File Name: " & FileName & vbNewLine & vbNewLine & _
       "Excel Workbook: " & ExcelWorkbookName & vbNewLine & vbNewLine & _
       "Excel Worksheet: " & ExcelWorksheetName & vbNewLine & vbNewLine & _
       "Excel Formula: " & ExcelFormula

' Pastes the information from the selected workbook into this workbook

    On Error GoTo ErrorMessage
    Range("A1") = "=" & ExcelFormula
    On Error GoTo 0

    With Range("A1")
        If IsError(.Value) = True Then
            .Value = .Value
        End If
    End With

' Open the selected workbook

    Workbooks.Open FullFileName
    Exit Sub
    MsgBox "There was an error!"
    GoTo ErrorExit
End Sub
Upvote 0
Well forum... I give up. :) I couldn't prevent that darn dialog from opening up. Luckily there's more than one way to skin a cat! I took a different approach to this macro and decided to open the workbook I want to pull information from instead of pulling the information without opening the workbook. Ultimately with a few bells and whistles my function works, feels, and presents wonderfully. Thanks all for any thought you may have given this problem.
Upvote 0

Forum statistics

Latest member

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