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.

 

[kryptonian]

You can use the activesheet.protect/unprotect function in vba to do this...

I assume you have a command button that you click on in your userform to enter the data you have typed to the database...

In the code for the command button, you can use this bit of code before and after the code that enters the data to the database, like this:

'start of code
ActiveSheet.Unprotect "password"

'==================
' your code to save to
'the database goes here
'==================

ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
'end of code

You must protect the active sheet with "password". What the code basically does is it unprotects the worksheet so your code can enter the data in the database then locks it up again afterwards.


I hope this helps.

 

[Smitty]

Welcome to the Board!

What kind of password did you apply that can be so readily dismissed?

Maybe you can post the code you have so far?

 

[itbman]

Welcome to the Board!

What kind of password did you apply that can be so readily dismissed?

Maybe you can post the code you have so far?

Here is the code. The security is on when I want it to be, and off when I want it to be, but I just wish there was some way to disable it from asking for the password at the start. One of my concerns is the the protection needs to be on if a user opens the file with macros disabled. So it should only be turned off in the macro and then turned on at the end of the macro actions.

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Unprotect Password:="12345"
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()
    UserForm1.Show
End Sub

Private Sub Label3_Click()

End Sub

Private Sub Label4_Click()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub TextBox4_Change()

End Sub

 

[Smitty]

What I meant was how are you protecting the workbook that it's asking for a password when you open it?

 

[itbman]

What I meant was how are you protecting the workbook that it's asking for a password when you open it?

i just click the protect sheet and select 'select locked cells' and 'select unlock cells'

 

[Smitty]

i just click the protect sheet and select 'select locked cells' and 'select unlock cells'

So worksheet protection in and of itself shouldn't be prompting you for a password when the workbook is opened or when the worksheet is activated.

What am I missing?

 

[itbman]

So worksheet protection in and of itself shouldn't be prompting you for a password when the workbook is opened or when the worksheet is activated.

What am I missing?

it only asks for a password if I set a password. If I put protect on and do not include a password, it won't ask for one. This sheet will be used by a number of people, so we need a password set to keep it safe.

 

[Smitty]

One of my concerns is the the protection needs to be on if a user opens the file with macros disabled. So it should only be turned off in the macro and then turned on at the end of the macro actions...[snip]...it only asks for a password if I set a password. If I put protect on and do not include a password, it won't ask for one. This sheet will be used by a number of people, so we need a password set to keep it safe.

But you seem to be talking about worksheet protection. That is not going to be affected by a macro when a workbook opens/closes unless you're using Open/Close events. The way you're unprotecting the sheet in your code is fine and will be seamless to users.

You are still not explaining what is causing a password prompt when the workbook is opened or a sheet activated.

 

[itbman]

But you seem to be talking about worksheet protection. That is not going to be affected by a macro when a workbook opens/closes unless you're using Open/Close events. The way you're unprotecting the sheet in your code is fine and will be seamless to users.

You are still not explaining what is causing a password prompt when the workbook is opened or a sheet activated.

Is there anyway I can send this file to you? I am not great at excel (as you can tell). I think if you could look at the sheet youself, maybe you can see what I am doing wrong. I thought the password prompt was due to the protect sheet option being selected and a password being setup. When I have protect sheet off, or do not input a password, there is no password prompt.