Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent data entry when macros are disabled (2003)

    I have prevented a user from entering data when macros are disabled in the past by hiding the worksheet on close and showing it with the on open functions. The bad part about this is when you close the workbook it asks for the user to save changes (the hiding of the worksheet). Is there a better way to restrict entry if a user selects disable macros? Thanks!!

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

    Re: Prevent data entry when macros are disabled (2003)

    The best way is to sign the VBA code with a digital signature. The user only has to accept ("trust") the signature once, thereafter macros will be enabled automatically. See Description of digital signatures and code signing in workbooks in Excel.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent data entry when macros are disabled (2003)

    I've looked at Digital Signatures before, but certificates are quite expensive. I noticed this time the part about SelfCert.exe in Microsoft Tools. After doing further research this should suit my needs since all of my worksheets are only used internally. Plus there is no cost involved.

    Thanks for the advice and link!!

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent data entry when macros are disabled (2003)

    With the SelCert.exe the only thing it prompted me to enter was a display name. Do you know how to edit (to add email address and other info) and delete these (I accidentally made two of the same one)? Thanks!!

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

    Re: Prevent data entry when macros are disabled (2003)

    I don't think you can add info such as an e-mail address to a certificate made with SelfCert.

    You can view, edit and delete certificates in Internet Explorer:
    Select Tools | Internet Options.
    Activate the Content tab.
    Click Certificates.
    Your certificates are listed in the Personal tab.
    Double click a certificate to edit it.

  6. #6
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent data entry when macros are disabled (2003)

    Perfect. Thanks!!

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent data entry when macros are disabled (2003)

    I would just use "Application.DisplayAlerts=False: in your workbook_close event.

    I do the same thing you're proposing to protect the Excel workbook in cases where macros are initially disabled. In my workbook_close code I hide (very hide) all sheets except one that has instructions on how to set their security. All sheets and the workbook are also protected (as if the code of course - and all with different passwords). If macros are enabled, this instruction sheet is never shown (it's hidden in workbook_open) since it's not needed.

    I agree on the digital certs (I use one I bought from Thawte - cheaper than VeriSIgn although they own Thawte) butI find users still get hung up since they refuse to read and don't check the box to 'always accept' when they first see the enable/disable macro message. In my experience, instructing them to set 'trust' a certificate is even more impossible since they can't find the security menu (the VIsualBasic toolbar isn't one most users have enabled). I've had many calls from people complaining they can't get something to work when the fix is right in the instructions they refuse to read (mine have more pictures than words).

    Deb

Posting Permissions

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