Copying an existing password protected sheet to a new workbook as an unprotected sheet gives the following error when user tries to type in data in the new worksheet.
Error: "the cell or chart you're trying to change is on a protected sheet"
This error happens only once. Click OK on the pop up error message and type again in a cell, then excel allows you to type data in the cells and save the sheet.
We have an excel (format .xls) file currently being used to create another excel spreadsheet when a button on a form in the same spreadsheet is clicked. It basically copies one password protected blank sheet (a template) to a new workbook as an unprotected sheet. The code below used to work with excel 2007(using .xls format). We recently upgraded from excel 2007 to excel 2013.
Private Sub cmdCancellation_Click()
Dim jBook As Workbook
Dim jsheet As Worksheet
CurrentWorkBook = ActiveWorkbook.Name
Workbooks(CurrentWorkBook).Unprotect jWorksheetPassword
'catch all for errors
On Error GoTo ErrEnd
Dim orginalScreenUpdating As Boolean
orginalScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
If Range("Language").Value = "2" Then
'French
Set jsheet = EPIPTemplateCancelFR
Else
'english
Set jsheet = EPIPTemplateCancelEN
End If
jsheet.Visible = xlSheetHidden
'jSheet.Visible = xlSheetVisible
'Delete this line
jsheet.Unprotect jWorksheetPassword
Set jBook = Workbooks.Add(xlWBATWorksheet)
jsheet.Copy After:=jBook.Sheets(1)
jBook.Sheets(2).Visible = xlSheetVisible
Application.DisplayAlerts = False
jBook.Sheets(1).Delete
Application.DisplayAlerts = True
jsheet.Visible = xlSheetVeryHidden
'Delete this line
jBook.Sheets(1).Unprotect jWorksheetPassword
'Delete this line
'jsheet.Protect Password:=jWorksheetPassword
NoErrEnd:
Workbooks(CurrentWorkBook).Protect Password:=jWorksheetPassword, Structure:=True, Windows:=False
Application.ScreenUpdating = orginalScreenUpdating
Unload Me
Exit Sub
ErrEnd:
Workbooks(CurrentWorkBook).Protect Password:=jWorksheetPassword, Structure:=True, Windows:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox DataTable.Range("MSG4").Value, vbCritical, DataTable.Range("MSG4TITLE").Value
Unload Me
End Sub
However the newly created sheet always shows the error message pop up shown in bold above whenever the user tries to enter in data in the new workbook. Any help or suggestions would be appreciated.
PS: please ignore the code where it says 'Delete this line
Bookmarks