Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to submit VBA form in a protected worksheet

0

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
excel
posted by: KarenS on Wed May 3, 2023 at 4:01 am
Answer
Discuss

Discussion

Hi Karen. 

Did you see my answer?
John_Ru (rep: 6142) May 5, '23 at 5:30 am
Add to Discussion

Answers

0

Hi Karen and welcome to the Forum.

You didn't attach a file to your question so I'm not sure which sheet you have protected.

If it's the worksheet  "1.PSR form" say (which you assign to varaible PSRfrm) and you've already protected it with a password "1234", just add the lines in bold below to unprotect, do things then reprotect the sheet (and do likewise for other sheets if necessary, changing "1234" to your real password):

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

' unprotect sheet

PSRfrm.Unprotect "1234"

'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

' reprotect sheet

PSRfrm.Protect "1234"

End Sub

Note that you can also set what type of sheet protection you want- e.g. see the Microsoft guidance here: Worksheet.Protect method or search the Tutorials section for other ways..

Hope this helps- if so, please remember to return and mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Wed May 3, 2023 at 8:34 am
Discuss

Discussion

No comment then Karen?
John_Ru (rep: 6142) May 12, '23 at 8:33 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login