+ Reply to Thread
Results 1 to 8 of 8

How to Have Two Cells Impacted by a Password Protected Cell

  1. #1
    Registered User
    Join Date
    04-29-2019
    Location
    Columbus, OH
    MS-Off Ver
    Office 2019
    Posts
    3

    Question How to Have Two Cells Impacted by a Password Protected Cell

    Hello all,

    I'm building a worksheet for approvals of expenses that need to be made, to help make my organization's system more efficient. I've been able to piggy back off of a previous thread that I found, in order to get part way what I need.

    However, even when the password is put incorrectly for the Approver Name, the Manager/Director Signature Picture still changes to that of the corresponding person that one was trying to select.

    How do I edit/add to the macro so that if the password is entered incorrectly, then the corresponding picture/signature cell will not populate, but instead, just become a blank cell?

    I've attached the dummy worksheet that I've been using to try and figure this out. Enjoy the Spongebob.

    I'm pretty new to programming so any words of advice would be great appreciated. Thank you.
    Attached Files Attached Files
    Last edited by Kamily; 04-30-2019 at 12:30 PM. Reason: Solved

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    Hi welcome to the forum,
    Piggy back off previous threads and other files is great but you need to know what your doing and what the code does.
    You mention you're pretty new at programming, how new?
    I'll take a look at Spongebob and see if I can help
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    04-29-2019
    Location
    Columbus, OH
    MS-Off Ver
    Office 2019
    Posts
    3

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    Thanks for the response, I tried to post the link to the previous thread earlier but since I'm a new member I was not able to. The code makes it so that in the dropdown list in order to access a name, each has a unique password associated to it.

    I also have an indirect function in place with the Approver Name cell (B13), so that when a name is chosen, a corresponding image in cell B10 shows up. However with the way the code is right now, even if a password is entered incorrectly and Approver Name is blank, the corresponding image of the intended name still shows up. I'd like to add a stop so that the indirect function does not process if the name password is not entered correctly.

    Here's the code I used:

    Option Explicit
    Const BobEsponja As String = "BobEsponja1"
    Const Calamardo As String = "Calamardo1"
    Const Patricio As String = "Patricio1"
    Const SenoraPuff As String = "SenoraPuff1"
    Const SenorCangrejo As String = "SenorCangrejo1"
    Const Plankton As String = "Plankton1"
    Const ArenitaMejillas As String = "ArenitaMejillas1"

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim pwd As String
    Dim Oops As Boolean

    Application.EnableEvents = False

    For Each cell In Target
    If Not Intersect(cell, Range("B13")) Is Nothing And cell <> "" Then
    pwd = Application.InputBox("Password for " & cell & ":", _
    "Enter Password", Type:=2)
    Select Case cell.Value
    Case "BobEsponja"
    If pwd <> BobEsponja Then Oops = True
    Case "Calamardo"
    If pwd <> Calamardo Then Oops = True
    Case "Patricio"
    If pwd <> Patricio Then Oops = True
    Case "SenoraPuff"
    If pwd <> SenoraPuff Then Oops = True
    Case "SenorCangrejo"
    If pwd <> SenorCangrejo Then Oops = True
    Case "Plankton"
    If pwd <> Plankton Then Oops = True
    Case "ArenitaMejillas"
    If pwd <> ArenitaMejillas Then Oops = True
    End Select

    If Oops Then
    MsgBox "Bad password"
    cell = ""
    End If
    End If
    Next cell

    Application.EnableEvents = True
    End Sub
    And by new, I mean I am still in my first month of playing around with it. It's not a part of my job at all, but I understand the very basics to follow instructions.

    Thanks a bunch

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,435

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    Change the last part of the macro with the OOPS test to

    Please Login or Register  to view this content.
    This will hide the picture with the signature if the password is wrong and show it if the password is valid

    It is not a matter of editing cells the pictures are linked to a named range and that named range will show the picture regardless of the password entered.
    And since the wanted end result is that the signature does not show if the wrong password is shown. the easiest solution is to hide the picture that holds the signature
    Last edited by Roel Jongman; 04-30-2019 at 11:12 AM.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    Try this one, I named the pictures BobEsponja, SenorCangrejo etc and added Blanco but did nothing with it
    The macro in Module2 GetTheImage places the image but you need to remove it first for the next run
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-29-2019
    Location
    Columbus, OH
    MS-Off Ver
    Office 2019
    Posts
    3

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    Thank you both! These both work like a dream and do exactly what I needed. I really appreciate the helps in figuring out the answers. Both of these methods work and is good to know for future use.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    De nada

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,543

    Re: How to Have Two Cells Impacted by a Password Protected Cell

    You should avoid Merged cells, it's a mess when selecting and coding but ... looks good
    I modified the code alittle and changed the way the picture is addressed, makes it easier to delete
    The picture name when copied is changed to PictureAs and the range's address
    for the corresponding picture to B13 the name is then
    Please Login or Register  to view this content.
    I added the inputbox module to mask the passwords its a code I found about 25 years ago
    Attached Files Attached Files

+ 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. Macro to copy cells to a password-protected spreadsheet
    By namy77 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-17-2017, 10:33 AM
  2. Row filtering negatively impacted by cell formatting
    By DoverwoodCS in forum Excel General
    Replies: 4
    Last Post: 03-24-2016, 11:51 AM
  3. Sort Cells in Password Protected Document
    By SaraStravers in forum Excel General
    Replies: 1
    Last Post: 03-20-2015, 07:41 PM
  4. Hidden Cells Password protected?
    By jamie.c in forum Excel General
    Replies: 2
    Last Post: 09-18-2007, 09:34 AM
  5. [SOLVED] Can specific cells on a worksheet be hidden & password protected?
    By Ann in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  6. [SOLVED] Can specific cells on a worksheet be hidden & password protected?
    By Ann in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-05-2005, 05:05 PM
  7. [SOLVED] password protected certain cells in spreadsheet
    By vv in forum Excel General
    Replies: 2
    Last Post: 02-09-2005, 07:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1