I am building a tool where users can create sheets for their individual projects that will later be used to automatically generate powerpoint slides. Everything worked great until I tried to add sheet protection to limit their editing to only the input cells required...
Here is the template sheet that gets copied with a new name whenever they add a new project
As you can see the sheet is protected, but I can edit the cells I need (D2 for example) because they're not locked
After I gather some user inputs via a form, I copy the template sheet to a new sheet in the same workbook with this code...wsShortBlank is the template sheet
I now have this sheet
The Done Editing button links to a sub that hides the current sheet and reshows the main menu form
But if I attempt to edit cell D2, I get this
As if the cell is locked. BUT, if I click over to another sheet and then click back to this sheet, I can edit cell D2 just fine.
What gives?
Here is the template sheet that gets copied with a new name whenever they add a new project
As you can see the sheet is protected, but I can edit the cells I need (D2 for example) because they're not locked
After I gather some user inputs via a form, I copy the template sheet to a new sheet in the same workbook with this code...wsShortBlank is the template sheet
Code:
wsShortBlank.Visible = xlSheetVisible
With ThisWorkbook
wsShortBlank.Copy After:=.Sheets(.Sheets.Count)
Set sh = ActiveSheet 'the copied sheet should be the active one, so grab a handle to it
End With
wsShortBlank.Visible = xlSheetVeryHidden
sh.Name = txtPE.Value & " " & txtPEC.Value
Unload Me
With sh
.Activate
.Unprotect password:=myPassword
'add done editing button
.Buttons.Add(468.75, 30, 210.75, 60.75).Select
Selection.OnAction = "doneEditing"
Selection.Characters.Text = "Done Editing"
'populate the data we know
.Range("B33").Value = comboClass.Value
.Range("B34").Value = txtPE.Value
.Range("B35").Value = txtPEC.Value
.Range("B36").Value = txtTask.Value
.Protect password:=myPassword
.Range("D2").Select 'stick the cursor in the first input field
End With
Application.ScreenUpdating = True
I now have this sheet
The Done Editing button links to a sub that hides the current sheet and reshows the main menu form
But if I attempt to edit cell D2, I get this
As if the cell is locked. BUT, if I click over to another sheet and then click back to this sheet, I can edit cell D2 just fine.
What gives?