Macro Prompt to protect

[ianripping]

Is there a way so that when a user exits a workbook a promt comes up if
a certain sheet hasn't been protected?

Eg Sheet Name = "Index"

If index hasn't been protected then promt comes up asking for user to
enter a password to protect that sheet. If it is protected then the
worksheet carry's on exiting.

 

[Tom Ogilvy]

If you mean closing the workbook, just use the BeforeClose Event. You can
check if the sheet is protected for content by looking at the

Worksheets("Special").ProtectContents property
If it is false, the sheet isn't protected. If you want to prompt for a
password, use the inputbox or build a userform to do this. To see the code
for protecting a sheet, turn on the macro recorder while you do it manually.

 

[Harald Staff]

Hi

Paste in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Pwd As String
Dim Bsaved As Boolean
If Sheets("Index").ProtectContents = False Then
Bsaved = Me.Saved
Pwd = InputBox("Please enter password for sheet Index:")
If Pwd = "" Then
Cancel = True
Else
Sheets("Index").Protect (Pwd)
If Bsaved Then Me.Save
End If
End If
End Sub

 

[ianripping]

That is some nice code Harald. Is it possible to make the characters t
appear as "*" instead of the word being visible when it is bein
inputted by the user

 

[Tom Ogilvy]

With readily built in functionality, no. The Inputbox doesn't support this
with a setting. Only the userform textbox has this as a readily accessible
built in capability.

 

[Harald Staff]

I haven't tested (or ever seen) this one in action, but check
http://www.xcelfiles.com/API_09.html
for a possible solution.

Remember also, defaut Excel behavior is "Please type password again", to
ensure that the user doesn't misspell or Jack Daniels-induced make things up
when typing. With **** it's more important than when you can see what you're
doing.

HTH. Best wishes Harald

 

[Tom Ogilvy]

I am sure Ian will have that up and running in no time.

--
Regards,
Tom Ogilvy

I haven't tested (or ever seen) this one in action, but check
http://www.xcelfiles.com/API_09.html
for a possible solution.

Remember also, defaut Excel behavior is "Please type password again", to
ensure that the user doesn't misspell or Jack Daniels-induced make things up
when typing. With **** it's more important than when you can see what you're
doing.

HTH. Best wishes Harald

 

[Harald Staff]

I am sure Ian will have that up and running in no time.

Hopefully not responding "I had a lot of files on my C drive and now they're
all gone"

Best wishes Harald