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,081,456
Messages
5,358,789
Members
400,513
Latest member
sdrowsick

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top