I have a user form that is being keyed in by multiple users. I have enabled password protected with certain range to be allowed.
However, upon doing so it triggers an error saying that the cell or chart is in a protected sheet.
How can I enable the form to be used for all worksheet with the password encrypted in it?
Is possible to encrypt the password without VBA and allowing the form to work?
None of the sheets are protected.
You can unprotect the sheet at the start of the code, run the code then re-protect it.Code
- Private Sub btnsubmit_Click()
- 'Copy input values to sheet.
- Dim irow As Long
- Const PW As String = "Secret" '<change password here
- 'Determine empty row
- 'emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
- With ActiveSheet
- .Unprotect PW
- irow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
- .Cells(irow, 1).Value = Me.txtequip.Value
- .Cells(irow, 2).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
- .Cells(irow, 3).Value = Me.txtupn.Value
- .Cells(irow, 4).Value = Me.txtbatch.Value
- .Cells(irow, 5).Value = Me.cmbchart.Value
- .Cells(irow, 6).Value = Me.cmbtrigger.Value
- .Cells(irow, 7).Value = Me.txtfail.Value
- .Cells(irow, 8).Value = Me.txtdisreview.Value
- .Cells(irow, 9).Value = Me.txtdateac.Value
- .Cells(irow, 10).Value = Me.txtempid.Value
- .Cells(irow, 11).Value = Me.txtduplicate
- .Protect PW
- End With
- 'Clear input controls.
- Me.txtequip.Value = ""
- Me.cmbdate.Value = ""
- Me.cmbmonth.Value = ""
- Me.cmbyear.Value = ""
- Me.txtupn.Value = ""
- Me.txtbatch.Value = ""
- Me.cmbchart.Value = ""
- Me.cmbtrigger.Value = ""
- Me.txtfail.Value = ""
- Me.txtdisreview.Value = ""
- Me.txtempid.Value = ""
- Me.txtduplicate.Value = ""
- End Sub
Alternatively, you can use ProtectWithUserInterFaceOnly
Excel is not really meant for multi users and you will probably encounter problems. You can use Collaborate if you have OneDrive and share the file that way.
Pleased to help