How to open a password protected file in a macro with user forms?

0
votes

I have a password protected excel file that I need to use in a macro, but I am afraid that the password will change from month to month. In order to access the file, I would like to create a user form to enter the password each time the macro is run. Here are my objectives for this sub:

  1. Select the file to open
  2. Enter the password in a user form (If the file is protected)
  3. Open the file using the selected path and password.

I can complete steps 1 and 3 on their own, but I am not sure how to incorporate the user form into this sub. How would you solve this problem?

1
excelpasswordsuserformvba
If the only info needed is the password, I would use an inputbox instead of a userform. – Scott Craner
I've never used an inputbox before. Thanks for the suggestion! It looks like it might just solve my problem nicely. I'll get back with my findings. – Bryan F

1 Answers

0
votes

Thanks to a tip from @Scott Craner, I was able to figure out a solution. Here's what I used:

If MsgBox("Is the APPS Invoice password protected?", vbYesNo, "APPS Invoice") = vbYes Then
    pass = Application.InputBox("Please enter the password now:", Type:=2)
End If

On Error GoTo ErrHandler
Set APPSInvoiceWB = Workbooks.Open(APPSInvoiceFN, Password:=pass)
On Error GoTo 0

I tested it out with a file that wasn't password protected and it didn't present a problem. I didn't find much on this topic through searches, so I hope that this helps others in the future!