Protecting All Files in Filepath/Protecting all sheets in workbook prior to saving as?

khlau

New Member
Joined
Jan 22, 2018
Messages
13
Hi All,

Wondering if someone could point me in the right direction and help please - I have a workbook that I generate other workbooks from, so from the dataset in the main tab a macro sorts the data and then for each name, generates another tab with all the respective data on. For example, "Joe Bloggs, and the report would list all of Joe Bloggs product lists".

Then, using another subroutine, I save each individual tab as it's own workbook. e.g Joe Blogg, Joe Bloggs 2 etc. This has worked well for my purpose, however I am looking to add a layer of user control by protecting certain cells etc. Is there a way to lock Sheet1 in ALL my generated files without doing it manually via VBA?

The generated file only has 1 tab in the file. Thanks for your time.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi @khlau,

Try this.
Change data in red for your information.

Code:
Sub Protecting_All_Files()
  Dim fPath As String, fFiles, wb As Workbook
  fPath = "[COLOR=#ff0000]C:\trabajo\books\[/COLOR]"
  fFiles = Dir(fPath & "*.xls*")
  Do While fFiles <> ""
    Set wb = Workbooks.Open(fPath & fFiles)
    wb.Sheets(1).Unprotect "[COLOR=#ff0000]abc[/COLOR]"
    wb.Sheets(1).Range("[COLOR=#ff0000]A2:D10, E3:D5[/COLOR]").Locked = True
    wb.Sheets(1).Protect "[COLOR=#ff0000]abc[/COLOR]"
    wb.Close True
    fFiles = Dir()
  Loop
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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