Issue with sheet protection

jerH

Board Regular
Joined
Dec 3, 2008
Messages
168
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


wbQzKqY.jpg



As you can see the sheet is protected, but I can edit the cells I need (D2 for example) because they're not locked


siLuSbx.jpg



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


owCBh45.jpg



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


4aJWgwj.jpg



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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top