PDA

View Full Version : Don't delete sheet if don't wirte a password in inputbox



Nader
03-15-2008, 03:46 AM
I want the workbook doesn't show the message that showed (below) when we want to delete a sheet unless write a password in the inputbox.

lucas
03-15-2008, 06:30 AM
There really isn't enough information here to address your question but to keep the message from showing you could try:
Application.DisplayAlerts = False
be sure to reset it to true at the end of your code.

Nader
03-15-2008, 07:54 AM
I trid this code in this event it show me the inputbox after click insert. How can I make it show the inputbox when i click on the delete.


Quote:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If InputBox("Enter Password:") = "hi" Then
Application.DisplayAlerts = False
Sh.Delete
End If

End Sub

Simon Lloyd
03-15-2008, 08:57 AM
How about something like this?, add this to the Thisworkbook module, activate a sheet and see what you get!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim IB As Variant
IB = Application.InputBox("Enter deletion code to allow possibility" _
, "Sheet delete activation!", , , , , 1)
If IB <> "1234" Or IB = "" Then
MsgBox "Sheet deletion not enabled"
ThisWorkbook.Protect Password:="Secret", Structure:=True
Else
MsgBox "Sheet deletion enabled"
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
ThisWorkbook.Unprotect Password:="Secret"
End Sub

lucas
03-15-2008, 09:07 AM
This may be hard to do. I see no before delete event. See the picture below.

You might try the ProtectStructure property of the workbook.....

If ActiveWorkbook.ProtectStructure = True Then
MsgBox "Remember, you cannot delete, add, or change " & _
Chr(13) & _
"the location of any sheets in this workbook."
End If

Simon Lloyd
03-15-2008, 09:19 AM
Lucas, there is no before delete event, you can't even check something like "If Activesheet.Delete = True" the best i could come up with was post #4 i used inputbox for simplicity but of course should be a combobox probably on a userform as you can't hide the characters in an inputbox.

Unless there is some system code you can check for upon delete being selected?

lucas
03-15-2008, 09:29 AM
Hi Simon, I guess I was still composing my post while you were posting....I agree no event for this. I have been searching the forum for a solution.....will try yours. Maybe we can get some input from the OP...

Simon Lloyd
03-15-2008, 09:35 AM
Do you happen to know of any Environ parameter or machine code that excel could possibly check for?

lucas
03-15-2008, 09:49 AM
Not off hand ......

If you have sheets you want not deleted you can always just protect the workbook. Then they could use a macro to unprotect and delete.

Simon Lloyd
03-15-2008, 11:11 AM
One thing missing from this disscusion.....the Op!