Solved

Trap Protect Message

Posted on 2013-01-23
2
167 Views
Last Modified: 2013-02-07
With Excel 2007, I use vba that is triggered by a button on the worksheet to copy the last row and past it into the row just below it.   I want to force the user to use the button to add a new row because of all the data validation.  The worksheet is protected except for rows that they've entered data in and the new row.  

If they try to start typing in a new row without using the button they get a message to use the button followed by the standard protect message prompting for a password.  Can I get rid of that standard message or change it?  

I know I could just past a lot of blank rows that would have the validation but I never know what the user will do and they might remove the validation so I prefer to use the button.  Or is there another approach I could consider?

--Caren
0
Comment
Question by:CarenC
  • 2
2 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38812787
Hi, Caren.

Please see the attached for a couple of options...

(1) Sheet 1
Please see the code below. Only the green cells are not locked. If the user tries to make a selection that includes a locked cell then a message  is displayed and cell A1 is selected. (If all of the attempted selection are locked the message is "Locked", otherwise it's "Mixed".)

It's by no means bullet-proof and is more annoying than the Excel messages.

(1) Sheet 2
Again, green cells are unlocked. The sheet simply uses the standard Excel Sheet Protection, with only "Select unlocked cells" enabled. If the user wants to insert new rows then they'll have to use your button.

Regards,
Brian.
[embed=file 630179]Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xLocked  As Variant

xLocked = Target.Locked

If xLocked = False Then Exit Sub

If IsNull(xLocked) Then
    MsgBox ("Mixed")
Else
    MsgBox ("Locked")
End If

Application.EnableEvents = False
    Me.Range("A1").Select
Application.EnableEvents = True

End Sub

Open in new window

Locked-Test.xls
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38865477
Thanks, Caren. Which option did you use?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Returning results from batch file 9 67
Excel Formula 4 28
ms office troubleshooting for users 8 35
Auto Adjust Percent rate 5 29
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now