Find and Replace in VBA Object (Excel 2002/2003)

Topic

New Reply

Hi

I have a large spreadsheet, all the worksheets except two have a password, which is the same for each sheet. The financial director wants to change the password.
A simple task for the worksheets, however there are event procedures in some of the sheets which unprotect the sheet, run the code and then protect the sheets again.

Is there a way to find and replace every occurence of the password in the project?. except the two which need to be protected but without a password.

Many Thanks

Braddy

Reply | Quote

Replies

The Replace dialog in the Visual Basic Editor has the option to search the entire project, so it’s easy to replace the password throughout the entire project in one fell swoop.

Reply | Quote

Hi Hans

When I select the project and go to edit everything is grayed out

Regards

Braddy

Reply | Quote

You must open at least one module before you can select Edit | Replace…

Reply | Quote

Hi Hans

Thanks for that it’s the first time I have attempted anything like that.

Regards

Braddy

Reply | Quote

For future consideration, and I realize nobody asked, consider using a constant for a password.

Suppose my password for protecting 20 of my 24 worksheets is “w1dget”. Rather than search and replace “w1dget” in code, I put the following in a module’s general declaration:

Public Const IGNOREME as String = "w1dget"

Whenever I need to supply my password in code, I refer to IGNOREME instead. Whenever the password is changed, I only need to change this one line in the declarations area, instead searching and replacing.

Your choice of a decent constant name will no doubt be better.

Reply | Quote

Hi FlyerMike

I like the look of this idea, thanks.

Braddy

Reply | Quote

I dunno if you had to look up that phrase (in one fell swoop) to type it in correctly, but congrats, as it is VERY CORRECT. This phrase is often mis typed and mis spelt….

See In one FELL swoop for the details…

Sorry to pirate the thread, but that phrase just stood out for me in Hans’s answer!!!

Reply | Quote