Protecting user from changing previous data

Hi All,

I have a block of data A1:D10 with Protection using  ALLOW USER TO EDIT RANGE.

The user enter data with the following order:
 Input 1:  A1, B1, C1, D1
 Input 2:   A2, B2, C2, D2
 Input 3:   A3, B3, C3, D3


I would like to Automatically prevent the user from changing ROW A1-D1 when 
Inserting NEW ROWS (Row A2-D2, Row A3-D3).

Thank you.
0
Utf
2/5/2010 11:25:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
642 Views

Similar Articles

[PageSpeed] 57

This can be a very complex issue if you don't think things out really well 
ahead of time.  What happens if, after they've typed data in A1:D1 and while 
entering more data in rows below row 1, they realize they made a mistake in 
one of the entries above it?  How do they correct the error?

How do you know they won't start in row 2 instead of row 1?  Is that not 
allowed at all?

Are the rows in your example already on the sheet for them to type into, or 
do they actually have to use Insert --> Row to get a new row?


"apache007" wrote:

> Hi All,
> 
> I have a block of data A1:D10 with Protection using  ALLOW USER TO EDIT RANGE.
> 
> The user enter data with the following order:
>  Input 1:  A1, B1, C1, D1
>  Input 2:   A2, B2, C2, D2
>  Input 3:   A3, B3, C3, D3
> 
> 
> I would like to Automatically prevent the user from changing ROW A1-D1 when 
> Inserting NEW ROWS (Row A2-D2, Row A3-D3).
> 
> Thank you.
0
Utf
2/6/2010 1:52:01 PM
Disregard the question about having to insert new rows.  I realize now that 
you said you have A1:D10 set up already to permit the data entries.

But we still have the questions about "what if they need" to correct an 
error in earlier rows, and one more I didn't mention before:

What if they don't completely fill a row initially?  Suppose they only make 
entries in A1 and B1 and then the next entry they make is in A2?  What do we 
do with C1 and D1?

"JLatham" wrote:

> This can be a very complex issue if you don't think things out really well 
> ahead of time.  What happens if, after they've typed data in A1:D1 and while 
> entering more data in rows below row 1, they realize they made a mistake in 
> one of the entries above it?  How do they correct the error?
> 
> How do you know they won't start in row 2 instead of row 1?  Is that not 
> allowed at all?
> 
> Are the rows in your example already on the sheet for them to type into, or 
> do they actually have to use Insert --> Row to get a new row?
> 
> 
> "apache007" wrote:
> 
> > Hi All,
> > 
> > I have a block of data A1:D10 with Protection using  ALLOW USER TO EDIT RANGE.
> > 
> > The user enter data with the following order:
> >  Input 1:  A1, B1, C1, D1
> >  Input 2:   A2, B2, C2, D2
> >  Input 3:   A3, B3, C3, D3
> > 
> > 
> > I would like to Automatically prevent the user from changing ROW A1-D1 when 
> > Inserting NEW ROWS (Row A2-D2, Row A3-D3).
> > 
> > Thank you.
0
Utf
2/6/2010 1:55:01 PM
Hi JLatham,

Thanks for your insight.

Perhaps a code such as this may suffice:

- Given data range is A1:D10 
- If    ANY    of the CELL in that particular row is FILLED (ex. A3/B3/C3/D3 
is filled)
  THEN....the aboved rows are not allowed to be EDITed (ex. ROW 1 and ROW 2) 
is PROTECTED
- If Need to change the above CELLs, then I will have to do one of these 
options
   1. Delete your Macro first, Change and then Put it back on
   2. Cut / Delete ALL Rows below it and put them back on.

==================

I know there are loopholes on that code, but I just want to prevent the 
users from changing the previous data.  At least, there are a couple more 
barriers.

More alternatives are appreciated.


"JLatham" wrote:

> Disregard the question about having to insert new rows.  I realize now that 
> you said you have A1:D10 set up already to permit the data entries.
> 
> But we still have the questions about "what if they need" to correct an 
> error in earlier rows, and one more I didn't mention before:
> 
> What if they don't completely fill a row initially?  Suppose they only make 
> entries in A1 and B1 and then the next entry they make is in A2?  What do we 
> do with C1 and D1?
> 
> "JLatham" wrote:
> 
> > This can be a very complex issue if you don't think things out really well 
> > ahead of time.  What happens if, after they've typed data in A1:D1 and while 
> > entering more data in rows below row 1, they realize they made a mistake in 
> > one of the entries above it?  How do they correct the error?
> > 
> > How do you know they won't start in row 2 instead of row 1?  Is that not 
> > allowed at all?
> > 
> > Are the rows in your example already on the sheet for them to type into, or 
> > do they actually have to use Insert --> Row to get a new row?
> > 
> > 
> > "apache007" wrote:
> > 
> > > Hi All,
> > > 
> > > I have a block of data A1:D10 with Protection using  ALLOW USER TO EDIT RANGE.
> > > 
> > > The user enter data with the following order:
> > >  Input 1:  A1, B1, C1, D1
> > >  Input 2:   A2, B2, C2, D2
> > >  Input 3:   A3, B3, C3, D3
> > > 
> > > 
> > > I would like to Automatically prevent the user from changing ROW A1-D1 when 
> > > Inserting NEW ROWS (Row A2-D2, Row A3-D3).
> > > 
> > > Thank you.
0
Utf
2/7/2010 12:18:01 AM
I think I can work something up that adheres to those rules.  Give me a day 
or so - other irons in the fire.

Also, see my response to your request for control of the user input.

"apache007" wrote:

> Hi JLatham,
> 
> Thanks for your insight.
> 
> Perhaps a code such as this may suffice:
> 
> - Given data range is A1:D10 
> - If    ANY    of the CELL in that particular row is FILLED (ex. A3/B3/C3/D3 
> is filled)
>   THEN....the aboved rows are not allowed to be EDITed (ex. ROW 1 and ROW 2) 
> is PROTECTED
> - If Need to change the above CELLs, then I will have to do one of these 
> options
>    1. Delete your Macro first, Change and then Put it back on
>    2. Cut / Delete ALL Rows below it and put them back on.
> 
> ==================
> 
> I know there are loopholes on that code, but I just want to prevent the 
> users from changing the previous data.  At least, there are a couple more 
> barriers.
> 
> More alternatives are appreciated.
> 
> 
> "JLatham" wrote:
> 
> > Disregard the question about having to insert new rows.  I realize now that 
> > you said you have A1:D10 set up already to permit the data entries.
> > 
> > But we still have the questions about "what if they need" to correct an 
> > error in earlier rows, and one more I didn't mention before:
> > 
> > What if they don't completely fill a row initially?  Suppose they only make 
> > entries in A1 and B1 and then the next entry they make is in A2?  What do we 
> > do with C1 and D1?
> > 
> > "JLatham" wrote:
> > 
> > > This can be a very complex issue if you don't think things out really well 
> > > ahead of time.  What happens if, after they've typed data in A1:D1 and while 
> > > entering more data in rows below row 1, they realize they made a mistake in 
> > > one of the entries above it?  How do they correct the error?
> > > 
> > > How do you know they won't start in row 2 instead of row 1?  Is that not 
> > > allowed at all?
> > > 
> > > Are the rows in your example already on the sheet for them to type into, or 
> > > do they actually have to use Insert --> Row to get a new row?
> > > 
> > > 
> > > "apache007" wrote:
> > > 
> > > > Hi All,
> > > > 
> > > > I have a block of data A1:D10 with Protection using  ALLOW USER TO EDIT RANGE.
> > > > 
> > > > The user enter data with the following order:
> > > >  Input 1:  A1, B1, C1, D1
> > > >  Input 2:   A2, B2, C2, D2
> > > >  Input 3:   A3, B3, C3, D3
> > > > 
> > > > 
> > > > I would like to Automatically prevent the user from changing ROW A1-D1 when 
> > > > Inserting NEW ROWS (Row A2-D2, Row A3-D3).
> > > > 
> > > > Thank you.
0
Utf
2/7/2010 2:19:02 AM
Try this combination of worksheet-event process handling codes.  The 
_Change() event processor will lock the cells, one by one from A1 to E3000 as 
they are used, while the _SelectionChange() event deals with forcing the 
focus to the next empty cell in that sequence.

Choose the worksheet, right-click the sheet's name tab and choose [View 
Code] and then copy and paste all the code below into the code module and 
edit it to set things like the password as needed.

Private Sub Worksheet_Change(ByVal target As Range)
'this depends on the Worksheet_SelectionChange() event
'handler to force the focus to the next cell that
'should have an entry placed into it, working
'through columns A:E, rows 1:3000 in sequence
'
'start by selecting all cells on the worksheet and
'using Format --> Cells --> [Protection] to 'Unlock'
'all cells so that entries can be made in them
'The cells will be locked, one at a time as
'entries are made into them
'
  Const sheetPassword = "myPassword" ' change as required

  If target.Column > 5 Or _
   target.Row > 3000 Or _
   IsEmpty(target) Then
    Exit Sub
  End If
  Me.Unprotect Password:=sheetPassword
  target.Locked = True
  Me.Protect Password:=sheetPassword
  
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
  Dim protectedRange As Range
  Dim anyCell As Range

  If target.Column > 5 Or _
   target.Row > 3000 Then
    'not in A:E, or below row 3000
    'ignore
    Exit Sub
  End If
  Set protectedRange = Me.Range("A1:E3000")
  For Each anyCell In protectedRange
    If IsEmpty(anyCell) Then
      If anyCell.Address = target.Address Then
        Exit For
      Else
        Application.Goto anyCell
        'to force scroll to the cell
        'use this instead
        'Application.Goto anyCell, True
        Exit For
      End If
    End If
  Next
  Set protectedRange = Nothing
End Sub


"apache007" wrote:

> Hi JLatham,
> 
> Thanks for your insight.
> 
> Perhaps a code such as this may suffice:
> 
> - Given data range is A1:D10 
> - If    ANY    of the CELL in that particular row is FILLED (ex. A3/B3/C3/D3 
> is filled)
>   THEN....the aboved rows are not allowed to be EDITed (ex. ROW 1 and ROW 2) 
> is PROTECTED
> - If Need to change the above CELLs, then I will have to do one of these 
> options
>    1. Delete your Macro first, Change and then Put it back on
>    2. Cut / Delete ALL Rows below it and put them back on.
> 
> ==================
> 
> I know there are loopholes on that code, but I just want to prevent the 
> users from changing the previous data.  At least, there are a couple more 
> barriers.
> 
> More alternatives are appreciated.
> 
> 
> "JLatham" wrote:
> 
> > Disregard the question about having to insert new rows.  I realize now that 
> > you said you have A1:D10 set up already to permit the data entries.
> > 
> > But we still have the questions about "what if they need" to correct an 
> > error in earlier rows, and one more I didn't mention before:
> > 
> > What if they don't completely fill a row initially?  Suppose they only make 
> > entries in A1 and B1 and then the next entry they make is in A2?  What do we 
> > do with C1 and D1?
> > 
> > "JLatham" wrote:
> > 
> > > This can be a very complex issue if you don't think things out really well 
> > > ahead of time.  What happens if, after they've typed data in A1:D1 and while 
> > > entering more data in rows below row 1, they realize they made a mistake in 
> > > one of the entries above it?  How do they correct the error?
> > > 
> > > How do you know they won't start in row 2 instead of row 1?  Is that not 
> > > allowed at all?
> > > 
> > > Are the rows in your example already on the sheet for them to type into, or 
> > > do they actually have to use Insert --> Row to get a new row?
> > > 
> > > 
> > > "apache007" wrote:
> > > 
> > > > Hi All,
> > > > 
> > > > I have a block of data A1:D10 with Protection using  ALLOW USER TO EDIT RANGE.
> > > > 
> > > > The user enter data with the following order:
> > > >  Input 1:  A1, B1, C1, D1
> > > >  Input 2:   A2, B2, C2, D2
> > > >  Input 3:   A3, B3, C3, D3
> > > > 
> > > > 
> > > > I would like to Automatically prevent the user from changing ROW A1-D1 when 
> > > > Inserting NEW ROWS (Row A2-D2, Row A3-D3).
> > > > 
> > > > Thank you.
0
Utf
2/7/2010 9:41:01 PM
Reply:

Similar Artilces:

can i carryover data automatically from page to page and how?
I am trying to set up a month-to-month data sheet. Can I set it up so that the accumulated total from one month carries over to the next? -- JimVT "JimVT" <JimVT@discussions.microsoft.com> wrote in message news:CA742954-561A-4714-AE23-DA5DD031656B@microsoft.com... >I am trying to set up a month-to-month data sheet. Can I set it up so that > the accumulated total from one month carries over to the next? > -- > JimVT Of course - at the top of sheet 2 in the required column type in =Sheet1!(Cell the total is in on sheet 1). if you are uncertain as to how to ...

After migrating user from Outlook 2002 to Outlook 2003 emails have disappeared
After migrating a user from Outlook 2002 to Outlook 2003, many of her emails have disappeared. I remember reading somewhere that this is not an uncommon side effect of this migration. Can anyone tell me how to reverse this? Is there a setting somewhere? KingCronos <test2005@bgop.org.uk> wrote: > After migrating a user from Outlook 2002 to Outlook 2003, many of her > emails have disappeared. I remember reading somewhere that this is not > an uncommon side effect of this migration. Can anyone tell me how to > reverse this? Is there a setting somewhere? Migrating properly wi...

Set exchange 2003 to log bouced emails (to non existent users)?
My exchange server bounces emails sent to a non existent user at my domain. I would like to log all bounced emails, so that I can create mail forwards for some of those incorrect emails addresses. Is it possible to log those incorrect email addresses? Cheers. The easiest would be have a copy of non-delivery reports sent to an account such as yours or postmaster@yourdomain.com. You add this in the Messages tab of the Properties for the SMTP Virtual server in Exchange System Manager. William Danny F wrote: > My exchange server bounces emails sent to a non existent user at my doma...

Changes to the global template
Recently I had to start a new username because of some corrupted files. I was able to save my documents. However, everytime that I quit microsoft office a dialogue box appears and says: "Word cannot save changes to the global template because it was opened with read only access. Do you want to save the changes in a template with a different name?" Initially I tried to save it, because I was afraid that the changes to my document would not be saved. Now I click save, and the next dialogue box appears: "Microsoft Visual Basic" "Run-Time errror "5995" Word c...

Split Forms
Is it at all possible to hide the Data Sheet portion of a split form at run time in 2007? The only solution I have found is to play with the SplitFormSize property, but I am afraid that it does not support different display sizes. Any suggestions ? Thanks ...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

How do I protect my comments from being edited by another user?
I regularly send excel worksheets to a co-worker via e-mail and then she sends it out to others within the company (sales force). There are occasions when she will need to revise it and send it back to me. How do I protect my comments so that they cannot be edited, or can I? I received a worksheet back today that the comments I had entered were changed. These comments are my back-up and I do not want them changed. Can anybody help? highlight the whole sheet format cells / protection / uncheck locked highlight the cells you want protected format cells / protection / check locked tool...

cell protection to allow GROUP/UNGROUP function
2 asthetic questions for all you advanced Excel Users.... Q1. is it possible to protect a worksheet, but still be able to us the +GROUP & -UN GROUP buttons ? to expand / contract the viewe ranges? Q2. anyone know how to change the colour of the AUTO FILTER button - i changes from black to dark blue when activated - i would like to hav this with more contrast and so it jumps out at you ( and the othe plebs in the office who keep forgetting) Regards David ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages dire...

Language change in outlook express
Hi, I cannot change th language to english in outlook express by defult it has French. please help me how to change in options. i check all my regional settings shows english(us) execpt in word and express. -- Nisha P nishkrish <nishkrish@discussions.microsoft.com> wrote: > I cannot change th language to english in outlook express by defult > it has French. Ask in an Outlook Express newsgroup. microsoft.public.outlookexpress.general -- Brian Tillman [MVP-Outlook] nishkrish Ask in the Outlook Express newsgroups Outlook Express with IE7 http://www.microsoft.com/co...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

2007 user levels
I only have a limited knowledge but have previously developed a number of relational databases using Access that have worked very well. If user level access has been removed in 2007 how do i stop some users accessing confidential data, while still allowing them to use other parts of the database. The data base will run on a simple pier to pier network, with one machine holding data and Access installed on all machines, so do not have MS Server. please help Mike" <Mike@discussions.microsoft.com> wrote in message news:EA03658D-236C-47BC-9F09-FC91674862D0@microsof...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

Insert Data From Multiple Worksheets Into One (With Criteria)
Ok, I have created a sales report list that tracks what customers we are contacting, how we are contacting them, if they are a new or existing customer and if we sent a quote, along with some other details. There is a worksheet the salesmen fills out for each day of the month (its linked to a calendar) and that all goes into a "dashboard" that shows the breakdown with graphs. Now what i would like to do is have another worksheet list all the customers that we sent quotes to so they can mark if the quote was won or lost , state sales projections and give reasons why won ...

change direction of cursor after enter
In excel 2003, how do I change cause the cursor to go the next cell to the right, rather than to go to the cell below? Thank you! Tools > Options > Edit > Move selection after Enter > select Right. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "duke" <jbrown2lt@msn.com> wrote in message news:9793815c-97dc-4894-9adc-f3c43d71c91d@i29g2000prf.googlegroups.com... > In excel 2003, how do I change cause the cursor to go the next cell to > the right,...

How do I protect one single cell?
I have a spreadsheet where people are going to type in numbers, and the final cell is a total, which of course I don't want people to accidentally type over, as it will lose the formula behind it. So...... I put some "data validation" behind that cell, to say only allow entries of text length = 50 - a kind of "mock" condition. Nobody'e ever gonna type in 50 chars. are they?! And sure enough, typing in (eg) 854221 brings up the error box to stop them, just like (eg) HHJSYT brings up the error box to stop them. I even unchecked the ignore blank" box, so that ent...

How-To: Last change date on row
When I set up a database I always include a last change date that auto updates every time any field in the record changes. Does anyone have a suggestion for how to achieve a similar function in a spreadsheet. i.e. put the current date/time in a field in the row whenever anything in the row changes? -- GGG ------------------------------------------------------------------------ GGG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29220 View this thread: http://www.excelforum.com/showthread.php?threadid=489447 GGG, For example, to put the last change date in...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

Windows 2008
Hi, I am an admin in windows 2008 server. However, I am not able to access the c:\Users\<user name>\Local Settings\History Anyone knows how to change the settings so I can access the folder? Thank you, "Jack Black" <gwklocker01@gmail.com> said this in news item news:60b578d9-ce73-4ab8-851b-9039ea6cfee5@n16g2000yqm.googlegroups.com... > Hi, > > I am an admin in windows 2008 server. However, I am not able to > access the > c:\Users\<user name>\Local Settings\History > > Anyone knows how to change the settings so I can...

joining data points on graph
I am merging two graphs and one has 23 data points, the other has 9. The category axis will have 23 data points but the 9 points are spread out, i.e. they may be at points 1, 3, 7, 15 etc of the 23-point scale. At the moment the graph will only join points that are beside each other; how do I get them to connect to the points that have spaces between them? minihana wrote on Fri, 10 Feb 2006 11:29:04 -0800: m> I am merging two graphs and one has 23 data points, the m> other has 9. The category axis will have 23 data points but m> the 9 points are spread out, i.e. they may be...

Stop running sum(Over All) when customer change
Dear All I hv report based on tblTransaction which contains many customers with their transactions by date wise. I grouped report on CustomerID sub Group by Fromat(trnDate,”MMM-YY”) to get monthly transactions for each customer. I made calculated field to obtained closing balance with running sum over Group. I want to make the Running sum – over all but when customers is changing it must stop and start again, by default it will give running sum of all the customer, exactly this I don’t want. Any idea to stopped or make running sum based on customer when month is changing it ...

Dynamic data label placement for graphs
I've read the article KB179199 on how to create custom data labels for charts and it's good, however doesn't meet all my requirements. Is there a way to have the data labels automatically move to a desired location relative to the graph? For instance, I'm using 50 bar graphs per work book and would like each data label to be just above their respective bar. TIA Hafeez I looked up article 179199 and got this: BUG: Grid Based on .QPR File Appears Blank in Visual FoxPro 5.0 which probably isn't the one you meant. By above the bar, are they horizontal bars and you ...

How to change labels on a chart from numbers to words?
Can Someone Please help me? I can't figure out how to change the labels on my chart in excel from numbers to words! I'm not sure which labels you want to change to words, but maybe Jon Peltier's charting tutorial will help you: http://www.peltiertech.com/Excel/ChartsHowTo/index.html Arin wrote: > Can Someone Please help me? I can't figure out how to change the labels on my > chart in excel from numbers to words! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html I suspect it's this page: http://peltiertech.com/...

List all users with Send AS rights
Hello All, I need to find a utility or script that will show all users who have the Send AS right enabled. I have looked at the script in Microsoft KB Article 912918 and it list all users who have full mailbox rights but do not have Send AS rights. What I need is to list only the users who do have Send AS rights. Is there anyhting that will tell me who has Send AS rights? -- RS MCSE, MCP +I MCP See if this helps. Auditing Send-as and Receive As rights via script http://gsexdev.blogspot.com/2005/12/auditing-send-as-and-receive-as-rights.html James Chong Rschraeger wrote: > Hel...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...