is this possible, replacing password protected sheet text with mac

[Rajat]

I am using Excel 2003 and have a file where there are 175 sheets, each sheet
is used to collect data for different project - but the same format is used
in each sheet to collect data, only difference between those sheet is 'Name
of Sheet' which is 1, 2, 3, 4 upto 175.

All those sheets are password protected using Data>Tools>protect sheet option
and my pass word is "project"

my problem is that by mistake i have written in cell $I$43 "Start Date" -
instead of this there should be "Amount" so i have to change this in all 175
sheet. For this i have to unprotect the sheet first and then type the text
and protect the sheet again. which is a time consuming process.

Can any one help me with a macro which can do this task ( in all 175 sheet
in cell $I$43 in place of text "Start Date" replace by "Amount" and keep the
sheet password protected) .

Thanx in advance

Rajat

 

[Mike Fogleman]

Sub unpro()
Dim w As Worksheet
For Each w In Worksheets
w.Unprotect ("project")
Range("$I$43").Value = "Amount"
w.protect Password:="project"
Next
End Sub

Mike F

 

[Rajat]

Dear Mike Fogleman

thanks for the quick reply, the code did not work properly
i have pasted the code in the module of VBA using Insert > Module but the
problem is when i run the macro it give this error -

Run-time error '1004'
Application-defined or object-defined error
and provide 3 option "End" "Debug" "Help"

but i noticed that the cell value on the active sheet is changed to "Amount".
have i done any mistake in pasting the code in VBA?

I hope you can rectify the proble with this explanation.

 

[Dave Peterson]

Try this modified version of Mike's code:

Option Explicit

Sub unpro()
Dim w As Worksheet
For Each w In Worksheets
w.Unprotect "project"
w.Range("I43").Value = "Amount"
w.Protect Password:="project"
Next
End Sub

Notice that there's a "w." in front of range("I43"). Without that "w.", then
range("I43") refers to the currently activesheet.

 

[Rajat]

thanks Dave

 

[Mike Fogleman]

Yeah, thanks Dave. I wasn't completely awake yet.