Excel Question... Again.

Reply Subscribe

Thread Tools

Apr 5, 2010 | 05:18 PM

stogie1020

Thread Starter

Needs more Lemon Pledge

Joined: Mar 2005

Posts: 52,768

Likes: 2,000

From: Phoenix, AZ

Excel Question... Again.

I have a workbook with 12 worksheets. Each is identical but named for the month it is to be used in. I restrict what can be done in each sheet and where a user can click, resize, etc. by protecting the sheet.

My problem lies in the fact that when I need to make a change, I need to make it 12 times, and I have to go through each sheet and unprotect, make the change, and the protect again with the password.

Is there an easier way to do this?

Small annoying problem: I have to unprotect each sheet individually and then re-protect them.

Large problem: I have to make 12 identical sheets and adjust each one when I want a change made...

Apr 5, 2010 | 05:37 PM

svtmike

Team Owner

Joined: Oct 2003

Posts: 37,701

Likes: 3,897

From: Chicago

You can only protect/unprotect sheets one at a time.

To make an identical change in several sheets at a time, select the tabs (through standard click + shift-click or ctrl-click methods) and type the change in once. It will occur in all of the selected worksheets.

Apr 5, 2010 | 05:54 PM

stogie1020

Thread Starter

Needs more Lemon Pledge

Joined: Mar 2005

Posts: 52,768

Likes: 2,000

From: Phoenix, AZ

Thanks. Unfortunately the time consuming task was the un-protecting and re-protecting each sheet...

Apr 5, 2010 | 06:08 PM

Pure Adrenaline

Dragging knees in

iTrader: (2)

Joined: Dec 2002

Posts: 12,434

Likes: 33

From: Seattle Area

This might not work, but...

Delete all sheets except one.
Make changes.
Copy the sheet 11 times.
Rename each sheet for corresponding month.

Would that not work?

Apr 5, 2010 | 06:20 PM

Anachostic

Safety Car

Joined: Jul 2007

Posts: 4,845

Likes: 145

Just because it's you...

Open Excel, Hit Alt-F11, open ThisWorkbook, paste this code in.

To run these, view the macros.

Code:

Sub ProtectEach()
    Dim s As Worksheet
    Dim pw As String
    
    pw = InputBox("Enter password")
    
    For Each s In Me.Worksheets
        s.Protect pw, True, True, True, True, True, True, True, False, False, False, False, False, True, treu, True
    Next
    
    MsgBox "Done."
    
End Sub

Sub UnProtectEach()
    Dim s As Worksheet
    Dim pw As String
    
    pw = InputBox("Enter password")
    
    For Each s In Me.Worksheets
        s.Unprotect pw
    Next
    
    MsgBox "Done."
    
End Sub

Apr 5, 2010 | 07:39 PM

#1 STUNNA

Sanest Florida Man

Joined: Aug 2007

Posts: 45,965

Likes: 11,759

From: Florida

^ he hid a macro virus in white text!

What if you just renamed the spreadsheet once a month?

Apr 5, 2010 | 07:46 PM

stogie1020

Thread Starter

Needs more Lemon Pledge

Joined: Mar 2005

Posts: 52,768

Likes: 2,000

From: Phoenix, AZ

Anachostic, that looks awesome!

Wait, why does it appear to round up the fractions of a penny and put them into your account?

Trending Topics

Noise (sounds like a tiny generator) while the car is off?

11

257
2013 RDX Didn't Pass Smog Permanent P3400 Code

14

304
Thinking of purchasing 3G RDX - how is the track pad? How is Android Auto...

36

1.2k
J37A1 Swap

7

116
Road Force balance

5

129

Apr 5, 2010 | 08:05 PM

#1 STUNNA

Sanest Florida Man

Joined: Aug 2007

Posts: 45,965

Likes: 11,759

From: Florida

Didn't they do that in Superman 3?

Apr 6, 2010 | 07:00 AM

Whiskers

Go Giants

Joined: Aug 2004

Posts: 70,003

Likes: 1,260

From: PA

Apr 6, 2010 | 07:22 AM

#10

rza49311

Drifting

iTrader: (1)

Joined: Feb 2006

Posts: 3,072

Likes: 8

From: Southern VA

I was going to say you could probably make a macro to unprotect the sheets then another to protect the sheets.

Apr 6, 2010 | 11:27 AM

#11

Bearcat94

AZ Community Team

Joined: May 2007

Posts: 32,488

Likes: 7,771

From: N35°03'16.75", W 080°51'0.9"

Quote:

Originally Posted by Anachostic

Just because it's you...

Open Excel, Hit Alt-F11, open ThisWorkbook, paste this code in.

To run these, view the macros.

....

Brilliant.

Apr 6, 2010 | 12:08 PM

#12

MWalsh9152

all work and no play

Joined: Sep 2006

Posts: 13,916

Likes: 134

From: Wakefield, Ma

if you wrap it up when you bring home the nasties, you wont have to worry about protecting the sheets

Apr 6, 2010 | 12:33 PM

#13

GIBSON6594

My Garage

Joined: Nov 2004

Posts: 13,386

Likes: 11

From: NY

Quote:

Originally Posted by #1 STUNNA

Didn't they do that in Superman 3?

officespace

Apr 6, 2010 | 01:10 PM

#14

johnnysquire

Racer

Joined: Nov 2007

Posts: 294

Likes: 0

From: West Chester, PA

In the spirit of teaching the OP how to fish - other repetitive tasks can be automated using the "record macro" function under the Tools->Macro menu. The recorded macro will look a lot like the one posted above.

Apr 6, 2010 | 03:22 PM

#15

stogie1020

Thread Starter

Needs more Lemon Pledge

Joined: Mar 2005

Posts: 52,768

Likes: 2,000

From: Phoenix, AZ

Thanks guys, I have experience recording and using macros, but I was concerned about the macro being accessible to the other users, whom I do not want to be able to un-protect the sheet. Every time I keep the macro in my personal workbook, it seems to end up being available to the general masses...