Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

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

    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
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

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

    Hi Hans

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


    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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!!!
    Regards,
    Rudi

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

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

    Hi Hans

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

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <pre>Public Const IGNOREME as String = "w1dget"
    </pre>


    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.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

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

    Hi FlyerMike

    I like the look of this idea, thanks.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •