+ Reply to Thread
Results 1 to 8 of 8

Password promt in drop down list to unprotect a protected sheet

  1. #1
    Registered User
    Join Date
    09-25-2019
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Question Password promt in drop down list to unprotect a protected sheet

    Hello Excel forum,

    I am relatively new to VBA and I am struggling with the following:

    I have a sheet that is protected with a password.
    I have a drop down list with io cell D7 with 2 selections: "Sales" or "Engineering".

    Basically, when Sales use the sheet, they will only use it in the protected mode with "Sales" already selected in the drop down menu. I would like to be able to select "Engineering" from the the drop down list which should then prompt me to input the password to unprotect the entire sheet and unhide the cells that are hidden. If the incorrect password is input, a box saying "incorrect password" should appear and hte sheet should remain protected as it already is.

    Note: Selecting "Engineering" from the drop down list when the sheet is not protected already unhides certain cells that only Engineering can use (hence why the sheet is protected for Sales as we do not want them to use the engineering cells)

    Thank you very much for your help with this

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,218

    Re: Password promt in drop down list to unprotect a protected sheet

    Try adding this to the worksheet's code module...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-25-2019
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Password promt in drop down list to unprotect a protected sheet

    Hi dangelor,

    Thanks for the reply. I've tried the code however it doesn't seem to work.
    I assume this is because, since the sheet is already protected, i cannot actually select "Engineering" in the drop down list in D7, therefore theTarget.Value will never be "engineering"??

    When i try to change the drop down list to "Engineering" i get the standard pop saying the "the cell you are trying to change is on a protected sheet [...] You might be requested to enter a password"

    Attachment 642815

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,218

    Re: Password promt in drop down list to unprotect a protected sheet

    Unprotect the sheet manually, then change that cell's format protection to unlocked before re-protecting the sheet.

  5. #5
    Registered User
    Join Date
    09-25-2019
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Password promt in drop down list to unprotect a protected sheet

    So this nearly works!

    The issue now is that once i select "engineering", it will unhide the cells that are hidden at the same time that the password promt comes up.
    Ideally, once Engineering is selected, only the password promt comes up and:

    1. if the password is correct, the sheet unprotects and these cells become visible
    2. if the password is incorrect, D7 reverts back to "Sales" leaving the hidden cells hidden


  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,207

    Re: Password promt in drop down list to unprotect a protected sheet

    I think this will do what you want. It uses Excel's password unprotect dialog box (with hidden characters) rather than an input box.

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  7. #7
    Registered User
    Join Date
    09-25-2019
    Location
    London, UK
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Password promt in drop down list to unprotect a protected sheet

    Right.

    Thank you both. with both the codes and a bit of logical thinking and conditional formatting i was able to do what I needed. I modified the codes a little as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sPassWord As String
    Const PW As String = "BFC"

    If Target.Address = "$D$7" Then
    If Target.Value = "Sales" Then
    Range("D8").Value = " "
    Me.Protect PW
    Else
    If Target.Value = "Engineering" Then
    sPassWord = Application.InputBox("Enter Password to unprotect worksheet.", "Worksheet Protected")
    If sPassWord = PW Then
    Me.Unprotect PW
    Range("D8").Value = 1
    Else
    Target.Value = "Sales"
    MsgBox "Incorrect password entered."
    Range("D8").Value = " "
    Me.Protect PW

    End If
    Else
    Me.Protect PW
    End If
    End If
    End If
    End Sub



    I effectively included a cell that i could then reference in the conditioanl formating to make my hidden cells only visible once hte passwordwas entered and the cell value changes


    Thanks again!!

  8. #8
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,218

    Re: Password promt in drop down list to unprotect a protected sheet

    Glad to help - it's how I learn!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Drop Down List in Protected Sheet
    By Lacey87 in forum Excel General
    Replies: 0
    Last Post: 02-21-2019, 10:43 PM
  2. Password Protected Drop Down List
    By Dasloak in forum Access Tables & Databases
    Replies: 1
    Last Post: 01-24-2019, 01:12 PM
  3. Create Drop Down List w/ Password Protected Options
    By ajbris in forum Excel General
    Replies: 1
    Last Post: 07-16-2018, 08:54 AM
  4. Drop down list is Unprotecting my protected sheet
    By chrisowen in forum Excel General
    Replies: 1
    Last Post: 01-17-2017, 01:36 PM
  5. [SOLVED] Ran Macro that Password Protected Sheets, Now won't Unprotect
    By pjdumont in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-06-2013, 02:17 AM
  6. Password protected command button to protect/unprotect and hide/unhide
    By cpercival in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2011, 03:27 PM
  7. [SOLVED] Check for Sheet Password Protected, Unprotect with multiple passwo
    By David in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2006, 01:25 PM

Tags for this Thread

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06569 seconds
  • Memory Usage 9,089KB
  • Queries Executed 15 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1