Can I completely protect a worksheet that has unprotected cells?

[sshaffer99]

Hello,

I am copying a worksheet from one workbook to another (consolidating several worksheets into 1 workbook). In the original format, the worksheet has several unprotected cells so when the worksheet is protected, these cells are unprotected.

After I copy the worksheet, I would like to make the entire sheet protected. Is there a way to do this without going into the individual cells that are currently unprotected and reversing?

Thanks.

 

[GTO]

Set a reference to the sheet, try like:

<font face=Courier New>    <SPAN style="color:#00007F">With</SPAN> Sheet1<br>        .Protect , <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">True</SPAN><br>        .Cells.Locked = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    </FONT>

 

[sshaffer99]

Thanks for your reply. When I inserted the code you provided, I get an error message: 424 Object Required.

Here is part of my code. Your suggested code is highlighed in red. Any ideas?

Application.DisplayAlerts = False
With ThisWorkbook
Workbooks(strPlatformFile).Sheets(strDataWs).Copy After:=.Sheets("Rollup")
.Sheets(strDataWs2).Name = strNewName
.Sheets("Rollup").Select
'========== MrExcel Code Begin
With strNewName
.Protect , True, True, True, True
.Cells.Locked = True
End With
'========= MrExcel Code End
End With
Application.DisplayAlerts = True

 

[GTO]

Hi there,

Let's see if I am reading your code correctly. You have (at least) two workbooks open - ThisWorkbook and another that is named whatever is in strPlatformfile. We'll call that file 'Source.xls'. In Source.xls there's a sheet, named whatever is in strDataWs (we'll call 'Source Sheet') that we want to copy after the 'Rollup' sheet in ThisWorkbook and lock all the cells on it and keep it protected. You also rename a sheet in ThisWorkbook, but I just REM'd that for the example.

If I have that right, I think the only issue is that you may have misunderstood what 'Sheet1' refers to in my example. See, Sheet1 w/o any quotes and such around it (like: ThisWorkbook.Worksheets("Sheet1")) is using the worksheet's CodeName rather than its tab name. You'll want to look up CodeName in help, as you'll find this handy for many things.

Anyways, I think you just need to be able to reference the copied sheet correctly. Since we know where we copied it to by position, we should be able to use .Index, somethinig like:

Option Explicit
    
Sub exa()
Dim strPlatformFile As String
Dim strDataWs As String
Dim strNewName As String
Dim wksData As Worksheet
    
    strPlatformFile = "Source.xls"
    strDataWs = "Source Sheet"
    strNewName = "Imported Sheet"
    
    With ThisWorkbook
    
        Workbooks(strPlatformFile).Sheets(strDataWs).Copy After:=.Sheets("Rollup")
        
        Set wksData = .Worksheets(.Worksheets("Rollup").Index + 1)
        
'        .Worksheets(strDataWs2).Name = strNewName
        .Worksheets("Rollup").Select
        
        '========== MrExcel Code Begin
        With wksData
            .Protect , True, True, True, True
            .Cells.Locked = True
        End With
        '========= MrExcel Code End
    End With
End Sub

BTW, if you use the code tags, like [code=rich] 'Your code...[/code], when posting code, it makes it easier to read.

Hope that helps,

Mark

 

[Bill C]

I'm new here so I don't know what to do to insert code (in a scrolling window) in a post. Where do I learn this and what are you using to do it?

Bill C

 

[GTO]

Hi Bill,

Just wrap [CODE][/CODE] around the code. If you press the <Go Advanced> button <GO Advance>for the reply, there's a little button that looks like a pound sign that'll do it for you.

The code tags will not colorize the code. That bit is from an add-in available. Blond memory prevents me from recalling where the link is at the moment.

Mark

 

[sshaffer99]

GTO - Yes, you had it exactly right and your solution worked perfect. Thank you so much!

It always amazes me how knowledgeable and helpful you are and all the other folks that answer questions on this forum. You get nothing back, yet you save many people many many hours of work, time and frustration.

Thank you, thank you thank you! Much appreciated.

 

[Bill C]

Thanks GTO that is just what I needed. now I can place code properly.

Bill C

 

[GTO]

GTO - Yes, you had it exactly right and your solution worked perfect. Thank you so much!

It always amazes me how knowledgeable and helpful you are and all the other folks that answer questions on this forum. You get nothing back, yet you save many people many many hours of work, time and frustration.

Thank you, thank you thank you! Much appreciated.

Well I'd have to disagree about the very knowledgeable part; when I read responses from Rory, Bob Phillips, or when Kenneth Hobson pops in with a "here's a DOS bit that'll do all that", as well as posts of many others, it is a constant reminder how much I don't know as well as how much is possible

Thank you for the 'helpful' part, I am certainly happy to share what little I has managed to 'stick' in my poor noggin

Thanks GTO that is just what I needed. now I can place code properly.

Bill C

Glad to help and welcome to the forum