Code to UNPROTECT then PROTECT my WORKBOOK when Macro runs - Can someone show me please???

[Barryoffshore]

Okay, I have this code and it works very well - but it only works if my WORKBOOK is unprotected. I know (thanks to this forum and ALL the GREAT people here) how to Unprotect then Protect an ActiveSheet, but I can't figure out how to Unprotect then Protect my workbook when the macro runs... Can someone please show me what I need to do?

Thanks!!! Bear

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets
WS.Activate
A1 = WS.Range("C2").Value
If A1 <> "" Then
If InStr(C2, " - ") Then
s = Split(Trim(E8), " - ")
WS.Name = s(0)
Else
WS.Name = WS.Range("C2").Value
End If
End If
Next
End Sub

 

[CharlesH]

HI,

Where does the code stop?
Also see if this works.

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
myPassword = "password"''''' Be sure you change this to your actual password.
Application.ScreenUpdating = False
For Each WS In thisworkbook.worksheets
    WS.Activate
    WS.Unprotect Password:=myPassword
    A1 = WS.Range("C2").Value
    If A1 <> "" Then
        If InStr(C2, " - ") Then
            s = Split(Trim(E8), " - ")
            WS.Name = s(0)
        Else
            WS.Name = WS.Range("C2").Value
        End If
    End If
    WS.Protect Password:=myPassword
Next
End Sub

 

[CharlesH]

Hi,

Amended code so that it will not look at "Retail" and "Summary" sheets.

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
myPassword = "password" ''''' Be sure you change this to your actual password.
Application.ScreenUpdating = False
For Each WS In ThisWorkbook.Worksheets
    If WS.Name <> "Rental Sheet Inst" And WS.Name <> "Summary Sheet" Then
        WS.Activate
        WS.Unprotect Password:=myPassword
        A1 = WS.Range("C2").Value
        If A1 <> "" Then
            If InStr(C2, " - ") Then
                s = Split(Trim(E8), " - ")
                WS.Name = s(0)
            Else
                WS.Name = WS.Range("C2").Value
            End If
        End If
        WS.Protect Password:=myPassword
    End If
Next
End Sub

 

[Barryoffshore]

CharlesH - Thank you again and a thousand times over for all your help - I keep getting an error (see below) and I've tried everything I can think of to fix it....... but as always, I can't.

(and i'm not worried about the password being seen here - I'll change it when we're done)

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
Application.ScreenUpdating = False
myPassword = "blm_rental"
For Each WS In ThisWorkbook.Worksheets
If WS.Name <> "Rental Sheet Inst" And WS.Name <> "Summary Sheet" Then
WS.Activate
WS.Unprotect Password:=myPassword
A1 = WS.Range("C2").Value
If A1 <> "" Then
If InStr(C2, " - ") Then
s = Split(Trim(E8), " - ")
WS.Name = s(0)
Else
WS.Name = WS.Range("C2").Value
End If
End If
WS.Protect Password:=myPassword
Next ----------------------------------------------------- Right here I get a "Compile Error - Next Without For"
End Sub

 

[Michael M]

Bear
If you learn to indent your code you'll see that your missing an End If, before the ws.protect line

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
Application.ScreenUpdating = False
myPassword = "blm_rental"
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Rental Sheet Inst" And WS.Name <> "Summary Sheet" Then
            WS.Activate
            WS.Unprotect Password:=myPassword
            A1 = WS.Range("C2").Value
                If A1 <> "" Then
                    If InStr(C2, " - ") Then
                        s = Split(Trim(E8), " - ")
                        WS.Name = s(0)
                        Else
                        WS.Name = WS.Range("C2").Value
                End If
        End If
    WS.Protect Password:=myPassword
    Next '----------------------------------------------------- Right here I get a "Compile Error - Next Without For"
End Sub

 

[Barryoffshore]

Micheal M - Thank you for your input and your help!!! I can always learn from people like you, CharlesH.

HOWEVER, this code gave me the same result - it says "Compile error in hidden module2. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application."

If it helps, I am using Excel 2010

Thanks again for all you and everyone has done for me and to help me.

Bear

Bear
If you learn to indent your code you'll see that your missing an End If, before the ws.protect line

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
Application.ScreenUpdating = False
myPassword = "blm_rental"
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Rental Sheet Inst" And WS.Name <> "Summary Sheet" Then
            WS.Activate
            WS.Unprotect Password:=myPassword
            A1 = WS.Range("C2").Value
                If A1 <> "" Then
                    If InStr(C2, " - ") Then
                        s = Split(Trim(E8), " - ")
                        WS.Name = s(0)
                        Else
                        WS.Name = WS.Range("C2").Value
                End If
        End If
    WS.Protect Password:=myPassword
    Next '----------------------------------------------------- Right here I get a "Compile Error - Next Without For"
End Sub

 

[Michael M]

Have you got a Sheet Change or Selection Change event macro that gets activated when this code is run ??
If so, that could be causing the problem
Try running the code manually, and see what triggers the Sheet module !

 

[CharlesH]

HI,

Part of the code is missing.

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
Application.ScreenUpdating = False
myPassword = "blm_rental"
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Rental Sheet Inst" And WS.Name <> "Summary Sheet" Then
            WS.Activate
            WS.Unprotect Password:=myPassword
            A1 = WS.Range("C2").Value
                If A1 <> "" Then
                    If InStr(C2, " - ") Then
                        s = Split(Trim(E8), " - ")
                        WS.Name = s(0)
                        Else
                        WS.Name = WS.Range("C2").Value
                      end if''''''  ADDED
                End If
        End If
    WS.Protect Password:=myPassword
    Next '----------------------------------------------------- Right here I get a "Compile Error - Next Without For"
End Sub

 

[CharlesH]

Hi,

Along with Michael M. You should indent your code. It will be easier to read and trouble shoot.

Also if you have the code in the "Worksheet" code module. You really should place it in a regular code module.
If you have code in "ThisWorkbook" module. I think you should only have code in it that you want to run when the workbook opens.
All other code should be in a regular module.

 

[Barryoffshore]

This code works to change the SheetName if the Workbook is unprotected. However, if the Workbook is protected I get the Run-time error ‘1004’ and it shows the bug is in the "WS.Name = WS.Range("C2").Value" line.

Also, if I run it without the sheet being protected, or the workbook being protected – it works to change the name on the active sheet only, but it goes through and protects all the sheets in the workbook, but not the workbook itself (which unprotecting the Workbook, changing the sheetname [in the active sheet] to the value of cell C2, then protecting the Workbook is what I’m needing it to do).

I’m absolutely stumped here…

Charles and Michael M, I can’t thank y'all enough for all you have done for me!!!

Bear

Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
Application.ScreenUpdating = False
myPassword = "blm_rental"
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Rental Sheet Inst" And WS.Name <> "Summary Sheet" Then
            WS.Activate
            WS.Unprotect Password:=myPassword
            A1 = WS.Range("C2").Value
                If A1 <> "" Then
                    If InStr(C2, " - ") Then
                        s = Split(Trim(C2), " - ") 
                        WS.Name = s(0)
                        Else
                        WS.Name = WS.Range("C2").Value
                      End If
                End If
        End If
    WS.Protect Password:=myPassword
    Next
End Sub

 

[Michael M]

I can't see anywhere in the code where you are protecting the Workbook..???
you'll need to do that to make it do as required

    ActiveWorkbook.Protect Structure:=True, Windows:=True