+ Reply to Thread
Results 1 to 2 of 2

Testing an unopened (or opened) worksheet for VBA Project protected

  1. #1
    Ken Loomis
    Guest

    Testing an unopened (or opened) worksheet for VBA Project protected

    I thought I was being cute when I added password protection to me VBA code.

    But now, I need to go in and modify all the reports created with that
    worksheet. I have written the code that will search for all files containing
    the VBA project that has the error in it. It deletes all the VBA code and
    modules and deletes all the buttons on the report itself and deletes the
    hidden sheets that the VBA used.

    That leaves each file intact as a completed report, but creates what we
    refer to as a "Distribution Copy" that opens without the need to
    "Enable/Disable" macros. In other words, after my code cleans it up, it is a
    simple workbook with the three sheets that comprise the original report that
    my VBA code generated.

    The latest version of this report generator will only allow the user to save
    a "Distribution Copy" that strips out all the macros, buttons and extra
    hidden sheets, so I expect to avoid this problem in the future.

    My code to clean the old files runs automatically the first time the user
    runs this new version. everything works well, except when it encounters one
    of the worksheets that was built with the 2 version of my program that used
    VBA protection. Then it just hangs.

    I am trying to come up with a solution and have these ideas:

    1) Just make a list of those files and deal with it later
    2) Just delete those files
    3) Just open them and delete the buttons, which is the only way the user can
    access the VBA anyway

    But, in order to do any of those, I need to know how to determine if the VBA
    in a workbook is password protected.

    Can anyone suggest solution for this? I am looking for any suggestions
    about how to test the files before (or after) I open them, and possible
    another solution to the three I have mentioned above. Unfortunately, I can't
    go around to each user's desk and manually fix this. And, unfortunately, I
    am the only person I interact with that even remotely understands what I am
    trying to do. I've just been asked to make it work and a few months ago, I
    knew nothing about Excel VBA.

    So, ask always, I really appreciate all your help.

    Ken




  2. #2
    Registered User
    Join Date
    08-20-2003
    Location
    Luton, England
    Posts
    63
    1. If your code "just hangs" without producing a message about the password you might be able to use On Error.

    2. A workbook which is not protected will still open if a non-existent password is supplied. Perhaps you could put the password in your code anyway.
    Regards
    BrianB
    Most problems are caused by starting from the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It's easy until you know how.
    -----------------------------------------

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1