I am trying to work out a way to unprotect worksheets where I have built a form with some formulas in rows along with other questions and dropdown lists.
I want to protect the worksheet and hide the formulas so users cannot break them.
The Submit Form VBA doesn't work when a worksheet is protected.
I was thinking I could add some code to use a password to unprotect the sheet at the beginning, then once the form has been Submitted and cleared then more code could password protect the sheet - all with the same form control button.
Any ideas on how to code this? I am a beginner in VBA.
Add unprotect worksheet here
Option Explicit
Sub Submit_Form()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim DestLastRow As Long
Dim PSRfrm As Worksheet
'set variables for destination sheet
Set wsCopy = ThisWorkbook.Worksheets("Form capture")
Set wsDest = ThisWorkbook.Worksheets("PSR table")
Set PSRfrm = ThisWorkbook.Worksheets("1.PSR form")
'Find first blank row in data table based on data in column A
'Offset property moves down 1 row
DestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'Copy & Paste Data
wsCopy.Range("A2:AG2").Copy
wsDest.Range("A" & DestLastRow).PasteSpecial Paste:=xlPasteValues
'Clear PSR form
PSRfrm.Range("D7").ClearContents
PSRfrm.Range("D9").ClearContents
PSRfrm.Range("D15").ClearContents
PSRfrm.Range("D25:D35").ClearContents
PSRfrm.Range("D51:D54").ClearContents
PSRfrm.Range("D66:D83").ClearContents
Add password protect worksheet here
End Sub