VBA to unprotect and reprotect worksheets

SaraB802

New Member
Joined
Feb 7, 2013
Messages
34
Still getting my head around VBA, and now having problems with unprotect and protect. Below is the code I have ad it worked until we password protected the worksheets to stop the users deleting the formulas. have looked at loads of posts but can't seem to fit the solutions into my code below:

Public Sub Update_SLA_C_Event()
Dim lngCalc As XlCalculation
Dim wkb1 As Excel.Workbook, wkb2 As Excel.Workbook, wkb3 As Excel.Workbook, wkb4 As Excel.Workbook, wkb5 As Excel.Workbook, wkb6 As Excel.Workbook, wkb7 As Excel.Workbook, wkb8 As Excel.Workbook

With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlManual
.EnableEvents = False
.EnableCancelKey = xlErrorHandler
End With

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Central EvePro2.xlsm")
Set wkb3 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Northern EvePro2.xlsm")
Set wkb4 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Southern EvePro2.xlsm")
Set wkb5 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Eastern EvePro2.xlsm")
Set wkb6 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Sabah EvePro2.xlsm")
Set wkb7 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Sarawak EvePro2.xlsm")
Set wkb8 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Events EvePro2.xlsm")
Call wkb1.Sheets("SLA C_Event").Range("B16:H248").Copy
Call wkb2.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb3.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb4.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb5.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb6.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb7.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)
Call wkb8.Sheets("SLA C_Event ").Range("B16").PasteSpecial(Paste:=xlValues)

Application.CutCopyMode = False
Call wkb1.Sheets("SLA C_Event").Range("J17:L35").Copy
Call wkb2.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb3.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb4.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb5.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb6.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb7.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)
Call wkb8.Sheets("SLA C_Event").Range("J17").PasteSpecial(Paste:=xlValues)

With Application
.ScreenUpdating = True
.Calculation = lngCalc
.EnableEvents = True
.EnableCancelKey = xlInterrupt
End With

wkb2.Close SaveChanges:=True
wkb3.Close SaveChanges:=True
wkb4.Close SaveChanges:=True
wkb5.Close SaveChanges:=True
wkb6.Close SaveChanges:=True
wkb7.Close SaveChanges:=True
wkb8.Close SaveChanges:=True
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Shadow123

Board Regular
Joined
Aug 20, 2012
Messages
124
simple way to go would seem to be to unprotect the workbooks.. i think its this to do that, but i don't have the protect function on excel in the office (ugh don't ask)

Code:
 wkb1.Unprotect Password:="111111"
wkb1.Protect Password:="111111"
 

Shadow123

Board Regular
Joined
Aug 20, 2012
Messages
124
SOrry that was for workbooks, maybe

Code:
sub testme()
* dim myPWD as string
* myPwd = "x"
* activesheet.unprotect password:=myPWD

* 'do your stuff

* activesheet.protect password:=mypwd
end sub
 

Festus Hagen

New Member
Joined
Aug 1, 2011
Messages
40
Use the .Unprotect and .Protect methods ...

Sub yoursub()
YourWorksheet.Unprotect ...

Do your thing here ...

YourWorksheet.Protect ...
.Close ...
End sub

-Enjoy
fh : )_~
 

SaraB802

New Member
Joined
Feb 7, 2013
Messages
34
I get the codelines to use, thank you. But I am having problems where to put them in the code I already have. As i don't activiate a worksheets anywhere I tried to edit the code lines so it goes

Call wkb2.Sheets("SLA C_Event ").Unprotect("rubyrocks").Range("B16").PasteSpecial(Paste:=xlValues)

but get an unexpected end error
I guess I can put the protect lines I will put in before

wkb2.Close SaveChanges:=True

 

Festus Hagen

New Member
Joined
Aug 1, 2011
Messages
40
Rich (BB code):
Set wkb8 = Workbooks.Open("C:\Users\hp\Desktop\Trade Marketing\Events EvePro2.xlsm")
wkb1.Sheets("SLA C_Event").Unprotect For each sheet to be modified
Call wkb1.Sheets("SLA C_Event").Range("B16:H248").Copy
Blah ... Blah ...
Rich (BB code):
wkb1.Sheets("SLA C_Event").Protect
wkb2.Close SaveChanges:=True

-Enjoy
fh : )_~
 

Forum statistics

Threads
1,181,944
Messages
5,932,904
Members
436,869
Latest member
ABGTH

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
Top