Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Unlock / clear cell if dropdown = Edit

Posted on 2014-08-19
6
Medium Priority
?
257 Views
Last Modified: 2014-08-20
Hi,

I have a range of cells (B31:B59) They have drop downs of "Delete", "No Change" or "Edit"


If B32 Edit is selected i want K31 to be first be unlocked (password "hello") and second have its contents cleared

Is there some code that can achieve this for me?

Thanks
0
Comment
Question by:Seamus2626
6 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 40270822
I would be happy to help you, but I don't understand your question. Can you try rephrasing it in a more clear way, and/or providing a sample file?
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40271047
This clearly needs a little more thinking through.

First, in order to provide the kind of protection you're requesting for the data in column K, the entire sheet must be password-protected, except for those cells you want regular entry, such as those in B31:B59.  However, if you had a method for prompting a user for a password in order to allow deletion of a related cell, by extension, the user will also know the sheet password and could then unprotect it at any time and bypass the security you're hoping to achieve.  So, you'll actually need TWO (2) passwords:  one for end-user use and another to actually protect/unprotect the sheet.

Second, you have three user options (No Change, Edit, Delete), and while you've stated the mechanics for allowing a related cell to be deleted, there still remains the need to allow a related cell to be edited (since "Edit" is an option).  This is trickier, since even if you prompt a user for a password to edit, this action would unlock the sheet completely, meaning that any part of the sheet could then be edited unless there is a separate set of code to capture/limit entry to the single, related cell in column K, after which, the sheet would be re-protected again.  As above, two different passwords are needed.

Here's the VBA code - called a Worksheet_Change event - that would accomplish this:
Option Explicit
Dim pw As String
Dim varValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B31:B59")) Is Nothing And Target.Rows.Count = 1 Then
        If Target.Value = "Delete" Then
            pw = InputBox("Enter password to delete.", "Password Required")
            If pw = "rumin8" Then
                Me.Unprotect Password:="obfuscate"
                Target.Offset(0, 9).ClearContents
                Me.Protect Password:="obfuscate"
            End If
        ElseIf Target.Value = "Edit" Then
            pw = InputBox("Enter password to edit.", "Password Required")
            If pw = "rumin8" Then
                varValue = InputBox("Enter new value:", "Edit Value")
                If varValue <> "" Then
                    Me.Unprotect Password:="obfuscate"
                    Target.Offset(0, 9).Value = varValue
                    Me.Protect Password:="obfuscate"
                End If
            End If
        End If
    End If
End Sub

Open in new window

I've created two passwords:  "rumin8" for users, and "obfuscate" for the sheet; you could obviously choose others.

I've attached an example file that demonstrates your requested behavior.  One thing that is immediately apparent is how cumbersome and painstaking any edits will become.  That may be your intended plan, but it could make it more difficult to manage than you might realize.  Play around with this and I think you'll see what I mean.

Let me know if you have any questions.

-Glenn
EE-Q-28500836.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40271922
FYI, you could protect the sheet in the Workbook_Open event using the UserInterfaceOnly:=True argument and then the code can clear the relevant cell(s) without needing to unprotect the sheet.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Seamus2626
ID: 40271932
Hi guys, thanks for your responses, Glen your code is slick, and i will be using this code again!

I will repost my q as having thought it through, my requirements are slightly different, thanks!
0
 

Author Closing Comment

by:Seamus2626
ID: 40271938
Hi guys, thanks for your responses, Glen your code is slick, and i will be using this code again!

I will repost my q as having thought it through, my requirements are slightly different, thanks!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40274100
@Rory...I didn't know about that option (i.e., UserInterfaceOnly).  That is very handy; thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question