Cannot get password to auto enter when opening file via VBA

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
177
Office Version
  1. 365
Platform
  1. Windows
I know I've done this before - I checked my VBA on other files (that work), and it should work. But, it doesn't. Can someone figure out what I might be doing wrong here?

Have a commandbutton to open a file. If the user is me or the file owner, then it opens it with the password. If the user is anyone else, then it opens it readonly. The password itself is in hidden cell O2. I double-checked, and yes, the cell does show the correct password. (In fact, you'll see where I did a msgbox to make sure that password was correct.)

What happens is the file opens, and pauses for me to enter the password, as if I didn't have the "Password:=Pwd" written in the line.

Is there something I'm just not seeing?

Thanks in advance for any help on this!

VBA Code:
Private Sub CommandButton3_Click()

' This macro opens the weekly Lab Rental file for edit (mmay or dmcknight only) or view
' Written by Melody October May
' Created May 26, 2020

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim Pwd As String
Dim NetWorkFolder As String
Dim MasterFile As String

Pwd = Range("O2")
NetWorkFolder = Range("D20")
MasterFile = Range("D23")


If Environ("username") = "mmay" Or Environ("username") = "dmcknight" Then

MsgBox Pwd

Workbooks.Open FileName:=NetWorkFolder & "\" & MasterFile, Password:=Pwd, ignorereadonlyrecommended:=True 'This is where I'm getting my error - it's stopping for me to enter the password at the file open box

Else

Workbooks.Open FileName:=NetWorkFolder & "\" & MasterFile, ReadOnly:=True, ignorereadonlyrecommended:=True

End If

End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Remove this line
VBA Code:
MsgBox Pwd
 
Upvote 0
Michael M - I removed the MsgBox line. I had added that there to troubleshoot my problem (hoping that maybe I had it looking in the wrong place and it wasn't getting the password right). The MsgBox did at least tell me that the Pwd variable was correct.

Having removed it, I'm still getting the same problem, where it won't auto-password into the file.

The_Macrotect - by "hidden cell", what I mean is that the password is in a cell that's in a hidden row, and sheet is protected with that cell locked and hidden. Can it be found if someone puts their mind to it? Yes. But, the data is not sensitive - I'm merely trying to keep any unskilled "helpers" from screwing up code.
 
Upvote 0
OK, after more searching, I found the answer. I didn't realize that there was a WriteResPassword to use when the file is passworded in that direction.

It's now working. Thanks for reading this over! :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top