Tuesday, May 08, 2012 5:24 PM
I need to save an Excel 2010 file to open with a password prompt for editing. If no password is entered, the user will only have the file opened as "Read Only". I have looked at all the various options of protection in Excel 2010, and I am not seeing a solution. This was a piece of cake in earlier versions of Excel.
Wednesday, May 09, 2012 8:27 AMModerator
I have found a way to accomplish that.
We can make a userform by ourselves. The userform looks like this:
Remember to set the “PasswordChar” property of the textbox in the userform to “*”.
Then double click the button “submit”. Copy the following code into the CommandButton1_Click event:
Private Sub CommandButton1_Click()
Dim bok As Workbook
Dim pwd As String
Dim pth As String
Dim file_name As String
Dim pth_name As String
pwd = TextBox1.Text
If pwd <> "123abc" Then
pth = ActiveWorkbook.Path
file_name = ActiveWorkbook.Name
pth_name = pth & "\" & file_name
Set bok = Workbooks.Open(pth_name, , ReadOnly:=True)
ActiveWorkbook.ReadOnlyRecommended = False
Note: “123abc” should be changed to your own password.
In the “ThisWorkbook” part, copy the following code:
Private Sub Workbook_Open()
Note: In my situation, the userform is named as UserForm2. Change it to your own userform name.
TechNet Community Support
- Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Monday, May 14, 2012 2:39 AM
Wednesday, May 09, 2012 1:46 PM
You can follow the thread below. It may help you.
Thursday, July 26, 2012 5:08 PM
I always have trouble remembering where this is too. No need for special code. Option is on the Tools menu which can be reached by saving your doc using 'Save As'. See instructions at below link.