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

B

Barryoffshore

Board Regular
Joined
Feb 21, 2005
Messages
73
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
HI,

Check this thread.
http://www.mrexcel.com/forum/excel-...ksheet-through-visual-basic-applications.html


Code: 
Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
myPassword = "password"
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets
    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
 
Last edited:
Upvote 0
CharlesH, thank you very much - but it doesn't work for some reason... It may be because I am using excel 2010 - I don't know. But thank you again for trying and for ALL the help you've given me over the past few weeks! You are truly a brilliant man!
 
Upvote 0
Hi,

Did you change the "Password" to yours? You need to change "password" to the actual password.
And it tested ok for 2010
 
Last edited:
Upvote 0
I keep getting and Error 400 - but I only get that if the Workbook is protected when I run the macro - if it is not protected, it works as if the unprotect / protect part isn't in the code.

Barryoffshore said:
CharlesH, thank you very much - but it doesn't work for some reason... It may be because I am using excel 2010 - I don't know. But thank you again for trying and for ALL the help you've given me over the past few weeks! You are truly a brilliant man!
Click to expand...
 
Upvote 0
Hi,

Hope fully some one can pick up on this have to go. Should be around tomorrow. Also I'm working on a "Userform" for you.
 
Upvote 0
I really need some help with this... please... This was posted last night, and I can't see why it doesn't work other than that I am using Excel 2010 maybe - I don't know...


Sub BLM_RENAME_SHEET() Dim WS As Worksheet Dim myPassword As String myPassword = "password" Application.ScreenUpdating = False For Each WS In ActiveWindow.SelectedSheets 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 WS.Protect Password:=myPassword Next End Sub</pre>
 
Upvote 0
sorry, this is the format that isn't working

Code: 
Sub BLM_RENAME_SHEET()
Dim WS As Worksheet
Dim myPassword As String
myPassword = "password"
Application.ScreenUpdating = False
For Each WS In ActiveWindow.SelectedSheets
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
284
Gajendran Yadhav
G
tlc53
  • Solved
Unprotect to run VBA - Why isn't it working??
Replies
1
Views
408
tlc53
tlc53
M
  • Question
Macro added to QAT running/opening original workbook
Replies
3
Views
388
Wookiee
Wookiee
S
  • Solved
Need help with 2nd Variable Like I have for my Archive macro.
Replies
4
Views
305
ShawnSPS
S
S
  • Question
Running a macro in workbook now running some of the macro in the personal.xlsb file!
Replies
7
Views
188
Sallyprit
S

Forum statistics

Threads
1,214,912
Messages
6,122,200
Members
449,072
Latest member
DW Draft

Share this page

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