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