(Selecting the sheet really isn’t necessary, but it makes it easier to see what the macro is doing.) Now see how you can convert this macro to protect all the worksheets in the workbook.Ĭopy the ProtectSheets macro from the text file, and paste it into a VBA module in the Chapter07 workbook.Ĭlick in the ProtectSheets macro, and press F8 repeatedly to step through the macro.

This macro assigns a reference to the first worksheet to the mySheet variable, selects that sheet, and then protects it. MySheet.Protect 'Password', True, True, True Here’s what it looks like: Sub ProtectSheets() The Flow text file includes a macro named ProtectSheets.

Suppose that you have a workbook containing budgets for ten different departments and that you want to protect all the worksheets. You must, however, protect each sheet individually.

Loop Through a Collection by Using a For Each LoopĮxcel allows you to protect a worksheet so that users can change only cells that are explicitly unlocked. Similarly, you can get amazing increases in productivity by converting a macro that runs once into one that runs thousands of times in a loop. Long before Henry Ford, and even before Marc Brunel, the economist Adam Smith reasoned that in a single day, a single worker could make only one straight pin, but ten people could subdivide the work and create 48,000 pins in the same day-an almost 5,000-fold increase in productivity.

Visual basic for excel for loop