Find & replace info in locked hidden sheet

**JamesT1** · 08-10-2011, 02:55 AM

Hi Guys

Is there a way for a user to find and replace information in a hidden locked sheet without the user having access to the sheet

many thanks

jim

**Marcol** · 08-10-2011, 03:10 AM

Try this example

Option Explicit

Sub ReplaceText()
    Dim shtHidden As Worksheet

    Set shtHidden = Sheets("Hidden")
    With shtHidden
        .Unprotect
        .Range("A1:C100").Replace What:="OldText", Replacement:="NewText", LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                                   ReplaceFormat:=False
        .Protect
    End With
End Sub

Adjust the range to suit your needs.

If your hidden sheet has a password then

.Unprotect Password:= "password"

and

.Protect Password:= "password"

You could also add input boxes to define what is to be replaced and with what.
In that case make "OldText" and "NewText" declared variables.

Hope this helps

**martindwilson** · 08-10-2011, 03:11 AM

cant see or replace ?thats generally the purpose of a hidden,locked sheet don't you think?

**JamesT1** · 08-10-2011, 03:24 AM

Hi martin

the reason I am asking is because I'm using a workbook to collate staff information, this information is input using a 'userform'.

the information includes things like their line manager etc.... the database will be locked and hidden, however if a manager leaves etc and we need to replace their names I would like to do so without opening up the hidden sheets

jim

**martindwilson** · 08-10-2011, 09:12 AM

ah you didnt mention that!

**Jennasis** · 10-14-2012, 07:18 AM

I am looking for something very similar to this, to look at 14 hidden sheets:

Sat (A), Sun (A), Mon (A), Tue (A), Wed (A), Thu (A), Fri (A), Sat (B), Sun (B), Mon (B), Tue (B), Wed (B), Thu (B), Fri (B)

and replace the text "Test2" in all formulae with a declared variable.

I need this, as I am creating a document that will reference the last 2 weeks previous month's data (pulled into weeks A and B) and will need to update the month each time a new sheet is created - weeks A and B need to be hidden to avoid confusion for the end user.

If would be very grateful for assistance! Please let me know if you need anything further in order to help me :o)

Jenn

**Cutter** · 10-14-2012, 12:43 PM

Hello Jennasis.

You have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.