CODEWITHSUNDEEP
vbaexcel
Jared Burton
Jared Burton

Reputation: 75

Disable Password Prompt when opening workbook through VBA

I have a master workbook that connects to multiple team workbooks to link together a large sum of data. Essentially, I use a database style list of dashboards with their keyword (being the employee in charge of the dashboard), the dashboard short name, the dashboard full path, and the dashboard password.

I want an employee to be able to change their password manually, then update the password through a quick macro on the master workbook.

It all works fairly decently, but the input password prompt to open a workbook appears if the password supplied is incorrect when trying to run the code. If you hit cancel, it runs exactly how it should, but it's inconvenient and a little confusing. Is there a way for me to stop that prompt from showing? It appears on the set wbDash = workbooks.open.... Line:

'Make sure password is correct
If PWChange = True Then 'PWChange:=Boolean
    On Error Resume Next
    Application.DisplayAlerts = False
    Set wbDash = Workbooks.Open(FileName:=(DashLocBox.Value + DashNameBox.Value + ".xlsm"), password:=PW1Box.Value)
    Application.DisplayAlerts = True
    If Err.Number = 1004 Then
        MsgBox "You have supplied an incorrect password. You cannot update the workbook with an incorrect password."
        Exit Sub
    ElseIf Err.Number <> 0 Then
        MsgBox "An unexpected error has occurred. Aborting..." & vbCrLf & vbCrLf _
            & "Err " & Err.Number & ";" & Err.Description
        Exit Sub
    End If
    wbDash.Close
    Err.Clear
    On Error GoTo 0
End If

Upvotes: 1

Views: 3256

Answers (1)

Jared Burton
Jared Burton

Reputation: 75

Linga answered it in the comment above. Just need to open it read-only. FML...

set wbDash = workbooks.open(FileName:=(DashLocBox.Value + DashNameBox.Value + ".xlsm"), password:=PW1Box.Value, readonly:=True)

Upvotes: 1

Related Questions