Cannot get password to auto enter when opening file via VBA

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
153
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Remove this line
VBA Code:
MsgBox Pwd
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
153
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.
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
153
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,208
Members
416,229
Latest member
mohammadmihdi

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
Top