Refering to close or cancel buttons on xlDialogOpen

VBA newbie · #1 03-11-2005, 06:19 PM

Hi,

I have the following code that allows a user to click a command button in my current workbook and then choose a file to open and then copy some data from into the current workbook. This works fine, but if the user doesn't select a file and presses either the close or cancel button instead then the code still runs and this causes a couple of problems. I want to use an if then statement but am unsure how to refer to the close or cancel buttons on the open dialog box in the code? Can anyone help?

Private Sub Cmd_file_select_Click()
Dim oldfile As String, newfile As String, x As String

oldfile = ActiveWindow.Caption

Application.Dialogs.Item(xlDialogOpen).Show

newfile = ActiveWindow.Caption

x = MsgBox("Have you opened the correct file, and is the data to be imported in columns A to C with the word END in the last cell of the firm name column? If so select Ok to import the file, or Cancel to return to Investor Solutions without importing", vbOKCancel)

Select Case x

Case vbOK
Windows(oldfile).Activate
Macro_undo_copy_original
Sheet3.Cmd_undo.Enabled = True

Sheet3.Activate
Range("A1").Select
Sheet3.Unprotect ("Code")
Columns("A:C").Select
Selection.Clear
Range("A1").Select

Windows(newfile).Activate
Columns("A:C").Select
Selection.Copy

Windows(oldfile).Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheet3.Cmd_update.Enabled = True
Macro_validation
Sheet3.Protect ("Code")

Windows(newfile).Close

Case vbCancel
Windows(oldfile).Activate
Windows(newfile).Close

End Select

End Sub

crispbd · #2 03-11-2005, 07:10 PM

If no file is returned (hits the close or cancel buttons)
the function returns a string: "False"

instead of:
Application.Dialogs.Item(xlDialogOpen).Show

try:

UserResponse = Application.Dialogs.Item(xlDialogOpen).Show

If UserResponse = "False" then Exit Sub

FileName = ActiveWindow.Caption

You might also check in to using a different open dialog such as:

FileName = Application.GetOpenFileName (....)

that way you can return the file full path more easily

Welcome to the Excel Forum

Forum Statistics