MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Unprotect/Protect in VBA


Posted by Robert on February 27, 2001 10:48 PM

I've got a workbook with three worksheets - Input Form, Database, and Statistics. I'd like to protect all but 15 input fields on the Input Form (which I have already "unlocked" in cell formatting). My VBA code (that someone helped me write - I'm learning)transfers the data from the Input Form to the Database, but it won't do it with the worksheet/workbook protection on. I'd like to add code to unprotect the worksheets and workbook prior to transferring the data to the database, and then re-protect the worksheets/workbooks. Can anyone help? Thanks!


Here's the current code:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Set DaBa = Sheets("Database")
If Range("E4").Value <> "" Then
DaBa.Activate
LR = DaBa.Range("a1").End(xlDown).Row + 1
DaBa.Range("A" & LR & ":L" & LR).Value = DaBa.Range("V1:AG1").Value
DaBa.Range("N" & LR).Value = DaBa.Range("AI1").Value
DaBa.Range("R" & LR & ":S" & LR).Value = DaBa.Range("AM1:AN1").Value

DaBa.Range("M" & LR - 1).Copy
DaBa.Range("M" & LR).Select
ActiveSheet.Paste
DaBa.Range("O" & LR - 1 & ":Q" & LR - 1).Copy
DaBa.Range("O" & LR & ":Q" & LR).Select
ActiveSheet.Paste
DaBa.Range("T" & LR - 1).Copy
DaBa.Range("T" & LR).Select
ActiveSheet.Paste
Else
MsgBox "You must have a Customer Name to enter a record."

End If
Sheets("Input Form").Select
Range("E4:E23").SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
Range("E4").Select
End Sub


I believe the codes I need are:
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
(to unprotect the worksheet/workbook)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
(to protect the worksheet/workbook)

but I think I'm just not putting them in the right place above because I keep getting an error. Can I just place them inline in the above code or do I need a separate subroutine for it? Any help is greatly appreciated!!

Robert


Posted by David Hawley on February 27, 2001 11:54 PM

DaBa.Range("M" & LR - 1).Copy DaBa.Range("M" & LR).Select ActiveSheet.Paste DaBa.Range("O" & LR - 1 & ":Q" & LR - 1).Copy DaBa.Range("O" & LR & ":Q" & LR).Select ActiveSheet.Paste DaBa.Range("T" & LR - 1).Copy DaBa.Range("T" & LR).Select ActiveSheet.Paste


Hi Robert

Try this:

Private Sub CommandButton1_Click()
CommandButton1.TakeFocusOnClick = False
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="secret"
Sheets("Database").Unprotect Password:="secret"
Set DaBa = Sheets("Database")
If Range("E4").Value <> "" Then
DaBa.Activate
LR = DaBa.Range("a1").End(xlDown).Row + 1
DaBa.Range("A" & LR & ":L" & LR).Value = DaBa.Range("V1:AG1").Value
DaBa.Range("N" & LR).Value = DaBa.Range("AI1").Value
DaBa.Range("R" & LR & ":S" & LR).Value = DaBa.Range("AM1:AN1").Value

DaBa.Range("M" & LR - 1).Copy
DaBa.Range("M" & LR).Select
ActiveSheet.Paste
DaBa.Range("O" & LR - 1 & ":Q" & LR - 1).Copy
DaBa.Range("O" & LR & ":Q" & LR).Select
ActiveSheet.Paste
DaBa.Range("T" & LR - 1).Copy
DaBa.Range("T" & LR).Select
ActiveSheet.Paste
Else
MsgBox "You must have a Customer Name to enter a record."

End If
Sheets("Input Form").Select
Range("E4:E23").SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
Range("E4").Select

Sheets("Database").Protect Password:="secret"
ActiveWorkbook.Protect Password:="secret"
End Sub

You may also be interested to know that you can potect a sheet with code and have it so any other VBA code will work on the protected sheet, but a normal user could not. You would do this with the code below:


Sheets("Database").Protect Password:="secret", _
UserInterfaceOnly:=True

But I suggest yoiu select "Protect" and push F1 to read the help on "UserInterfaceOnly:=True"

OzGrid Business Applications

Posted by Robert on February 28, 2001 11:26 AM

Dave,

Many thanks! Worked like a charm.