Temporarily Disable "Select Sheet" Dialog

[mjseim]

Greetings,

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!

Thanks.

 

[venkat1926]

from what I understand your pronlem

in the oode add a statement at a suitable place
on error goto line1
you introduce a line
line1with 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.

 

[mjseim]

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
            .ClearContents
        Else
            .Value = .Value
        End If
    End With



'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
' Open the selected workbook

    Workbooks.Open FullFileName
    
    
ErrorExit:
    Exit Sub
ErrorMessage:
    MsgBox "There was an error!"
    GoTo ErrorExit
End Sub

 

[mjseim]

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.