Disable Password Prompt Upon Opening Spreadsheet

[itbman]

have a password protected spreadsheet with a userform for people to enter data. This allows them to enter data, but stops them from editing or deleteing data once entered. When the spreadsheet is opened, it asks for a password to unlock it. Hitting cancel allows the user to continue and the userform pops up for the user to enter data. I would like to disable the initial password prompt when the file is opened to avoid confusion on the users part.

 

[Smitty]

Check your Private Messages.

 

[Smitty]

OK (and this is for anyone who finds themselves in a similar situation), you didn't mention that you were using UserInterfaceOnly protection argument in your Open event. Your problem lies in the fact that you didn't include the password there, so as the open event fires, it's looking for the missing password to the already protected sheet. The following will fix that:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    ActiveSheet.Protect Password:="12345", UserInterfaceOnly:=<SPAN style="color:#00007F">True</SPAN><br>    UserForm1.Show<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Look for an amended wb in the mail, along with a few suggestions/changes.

Have a great weekend,

 

[itbman]

OK (and this is for anyone who finds themselves in a similar situation), you didn't mention that you were using UserInterfaceOnly protection argument in your Open event. Your problem lies in the fact that you didn't include the password there, so as the open event fires, it's looking for the missing password to the already protected sheet. The following will fix that:

Private Sub Workbook_Open()
ActiveSheet.Protect Password:="12345", UserInterfaceOnly:=True
UserForm1.Show
End Sub

Look for an amended wb in the mail, along with a few suggestions/changes.

Have a great weekend,

thanks big time for the help. It is 99% there, just one small problem. After the userform is closed, the wb is protected, but the user only has to click on the the 'Unprotect Sheet' button and it unprotects without prompting for a password. This is the final hurdle, and this beast is finally done. Thanks for all the help you've given me.

 

[Smitty]

When you unload the UserForm are you adding the password back?

Unload UserForm1
Worksheets("Sheet1").Protect Password:="12345"

 

[itbman]

When you unload the UserForm are you adding the password back?

Unload UserForm1
Worksheets("Sheet1").Protect Password:="12345"

yes I do. That's what makes no sense....Here's the code, see if you can see any issues, but it looks right to me.

Private Sub CommandButton1_Click()

If TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Then
MsgBox "You are missing data"
Exit Sub
End If

' Find next empty row
n = Sheets("Sheet1").Range("A65536").End(xlUp).Row + 1

Range("A2").Value = 1

' Write values to next empty row
Cells(n, 1).Value = n - 1
Cells(n, 2).Value = TextBox2.Value
Cells(n, 3).Value = TextBox3.Value
Cells(n, 4).Value = TextBox4.Value


Rows(n & ":" & n).EntireRow.Select

' Clear all textboxes
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

'Display RRV code
MsgBox "Your RRV code is " & n - 1

End Sub

Private Sub CommandButton2_Click()
' Remove textbox
Unload UserForm1
Worksheets("Sheet1").Protect Password:="12345"
End Sub

Private Sub Workbook_Open()
ActiveSheet.Protect Password:="12345", UserInterfaceOnly:=True
    UserForm1.Show
End Sub

edit: got it! in the code for 'This Workbook' I had

Private Sub Workbook_Open()

'ActiveSheet.Protect UserInterfaceOnly:=True
UserForm1.Show

End Sub

tried commenting it out and it works perfect now