Handling incorrect password on file open

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Hi,

I have a macro that prompts the user for an Excel workbook and displays the file open dialog as follows...
Code:
  NewFileName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*", Title:="Please select file for import")
  If NewFileName = False Then
   MsgBox ("No file selected" & vbCrLf & vbCrLf & "No data has been imported"), vbCritical, "Import Abandoned"
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
   Exit Sub
  End If

' Code to handle input file is here
How do I detect an incorrect password so I can pop up a message and get them to try again?

Thanks
 

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Hi Andrew,

This is the code I am using...
Code:
  NewFileName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*", Title:="Please select file for import")
  If NewFileName = False Then
   MsgBox ("No file selected" & vbCrLf & vbCrLf & "No data has been imported"), vbCritical, "Import Abandoned"
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
   Exit Sub
  End If
  Workbooks.Open FileName:=NewFileName, UpdateLinks:=False

' Check Invalid password here?  

' Check if a valid file
  If Left(ActiveWorkbook.Name, 23) <> "New Business Calculator" Then
' Not a valid New Business Calculator sheet, so stop import
   ActiveWindow.Close
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
   MsgBox "You have selected the following file..." & vbCrLf & vbCrLf & NewFileName & vbCrLf & vbCrLf & "...this file is not valid for use with this process", vbCritical, "Invalid Data File"
   Exit Sub
  End If
...and I am assuming that I can check for a valid poassword where indicated.
Regards
Steve
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Not well tested, but maybe something like:
Rich (BB code):
Option Explicit
    
Sub exa()
Dim wb As Workbook
Dim sFileName As String
Dim lErrCounter As Long
    
    sFileName = Application.GetOpenFilename("Excel Pre-2007 Workbooks (*.xls), *.xls", , "", , False)
    If Not sFileName = "False" Then
        On Error GoTo WrongPWD
        Set wb = Workbooks.Open(Filename:=sFileName, UpdateLinks:=False)
        On Error GoTo 0
    End If
Exit Sub
WrongPWD:
    
    If Err.Number = 1004 Then
        lErrCounter = lErrCounter + 1
        If Not lErrCounter >= 3 Then
            If MsgBox("You entered an incorrect password or left it blank.  Do you want to" & vbCrLf & _
                      "try again?", vbQuestion Or vbYesNo, vbNullString) = vbYes Then
                
                Resume
            Else
                'any cleanup
                Exit Sub
            End If
        Else
            MsgBox "Yeh, okay, three shots is enough!", 0, vbNullString
            Exit Sub
        End If
    Else
        MsgBox "An unspecified error occurred.", vbCritical, vbNullString
        'any cleanup
        Exit Sub
    End If
End Sub
Hope that helps,

Mark
 

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Mark,

Thanks very much, that works nicely

Just one general question...

Does error handling have to be triggered by "On Error ..." or can you do a specific test without using On Error, such as
Code:
If Err.Code = 1004 then
 MsgBox etc etc
Else
 Continue processing
Endif
Thanks anyway

Steve
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Mark,

Thanks very much, that works nicely

Just one general question...

Does error handling have to be triggered by "On Error ..." or can you do a specific test without using On Error, such as
Code:
If Err.Code = 1004 then
 MsgBox etc etc
Else
 Continue processing
Endif
Glad to help Steve. At least thru 2003, there's no such thing as .Code for Err. You can use Err.Number, but you still would need to change On Error to On Error Resume Next, or the error is fatal (code stops).

A simple example to try:
Rich (BB code):
Sub exa()
Dim i As Integer
    
    'On Error Resume Next
    
    i = 100000 '< overflow
    If Err.Number = 6 Then
        MsgBox "We made it here"
    End If
    
End Sub
I would strongly caution against using On Error Resume Next for any more lines of code than aboslutely necessary. It masks errors and will cause you to spend much unnecessary time tracking down some tiny glitch...

Mark
 

Forum statistics

Threads
1,084,735
Messages
5,379,509
Members
401,608
Latest member
nanana2020

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top