Prevent Changing Data In Column But Allow Row Insertions / Deletions
Jul 31, 2012
I am trying to create a worksheet_change event that will prevent a user from changing or deleting data in the totals column of my worksheet yet allow them to insert rows and delete rows on the worksheet. I have a few issues I can't get resolved. First, if I delete a row and then click the undo message, I get my error message about trying to change the totals. Second, I cannot copy and insert copied rows. I can only insert blank rows. Here's my code and data.
Private Sub Worksheet_Change(ByVal Target As Range)
Static lngRow As Long
Dim iStrtRow As Integer, iEndRow As Integer, rRowMrkr As Range
[Code]....
Defined range names: "Header" in cell A1 and "Totals" in cell A12
View 1 Replies
ADVERTISEMENT
May 13, 2013
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies
View Related
Jun 15, 2009
I want to allow users to select a cell in a column, but not allow them to change the contents of the cell. Also, don't want to allow them to select all the cells on the sheet.
I'm guessing that I need to use the Change event, and restore the contents back to what they were before the change.
Is there a better way to do this?
View 10 Replies
View Related
Mar 16, 2007
I have been searching for a way to prevent a user from changing a worksheet name. I ran into some code from the post below (see link) but I find that the code lets the user change the name and save the changed name of a sheet; only when the user selects a different sheet does it change the name back to the set name (sname) from the code.
I'm sure there must be a way to make this happen without deactivating the sheet, but I can't figure it out. Is there some other sort of event that could cause this to happen.
View 9 Replies
View Related
Jan 11, 2007
I would like to write a macro that performs the following function:
I have a value in cell A1
In cell A2, I have the formula "=A1"
I want to make it so that cell A2 can never be directly changed; the only way to change the value in A2 is by changing A1. A2 should always equal A1.
I want a message box to pop up and alert the user whenever they attempt to modify A2, instructing them that if they wish to change A2, they have to change A1.
I had written this macro, and it kinda does the trick:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a2") Range("a1") Then
MsgBox "text goes here"
Range("a2").Value = Range("a1")
End If
End Sub
When I attempt to change cell A2, I get the MsgBox popup and it works how I want it to. However, when I alter cell A1, I get the same popup before cell A2 updates (to be precise, the popup occurs after I click another cell). I would like this to be immediate and without a MsgBox.
View 9 Replies
View Related
Mar 30, 2009
When I enter this : =now(). it gives the local date and time in my PC in that exact cell. assume it is in (A2). when I enter in Any cell in the spreedsheet the sam formula =now(). then the previous formula (A2) is changed to the new date and time.
View 5 Replies
View Related
Jul 30, 2009
I've got an issue where Formulas Referencing Cells on another sheet are getting changed automatically to refer the the moved cell data. The formulas on a sheet (2) refer to specific cells on sheet (1) within my workbook. I update Sheet(1) data with a macro. When sheet (1) updates, the present data gets shifted to the right.
In other words, I need to prevent the formulas from changing to reference the moved data values and keep referencing the initial cell with the new values. xample of change that occurs with update:
Correct:
View 3 Replies
View Related
Jun 17, 2014
I have a vlookup which checks another sheet and imports data. The formula is as such:
=IF(ISNA(VLOOKUP(B17,Data!A$1:$C$40,3,FALSE)),"",T(VLOOKUP(B17,Data!A$1:$C$40,3,FALSE))) .
The problem I have is when I paste and amend data on sheet Data! it changes the formula to
=IF(ISNA(VLOOKUP(B17,Data!A$1:$B$40,3,FALSE)),"",T(VLOOKUP(B17,Data!A$1:$B$40,3,FALSE))) .
How do I prevent it from doing this?
View 4 Replies
View Related
May 26, 2009
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
View 9 Replies
View Related
Jul 11, 2006
On my excell I have a lot of data on sheet 2 that i automatically pull form a database.
I have this fomula in a cell on sheet 1.
= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to
=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))
View 9 Replies
View Related
Apr 21, 2008
What I have is basically a form on the main sheet. Here other users can add and/or delete information in a form. I refer to the information in another sheet with formulas.
Now everything was great when using protections/ validations, to the where they fill in the information, but the problem occur when they are allowed to delete and remove rows.
This is something that I want to allow them, even though I do realise that this is something that many advice against it.
To begin with I found the post about changing relative references to absolute references (ConvertFormula). Still the formulas changes when rows are added or deleted and I would have to redo all formulas and run the macro again. I then found posts advicing on the use of the INDIRECT forumla. This formula was exactly what I needed as the formulas would not alter when rows were added/deleted. Now the problem is a combination of the use of INDIRECT and the the ConvertFormula. Now if I write a formula, for example:
=INDIRECT("'Sheet1'A1")
Then when I try to expand the formula over to the rest of the cells, then the references stay pointing at A1, when I want them to continue pointing at A2, A3, etc...
Now what I am looking for is some way to tell all my formulas, after expanding the original formula, that they should all have the INDIRECT function. If it was possible it would be great if you could implement it in the same way as with the absolute reference, for example:
For i = 1 To MyRange.Areas.Count
MyRange.Areas(i) = Application.ConvertFormula(MyRange.Areas(i).Formula, xlA1, xlA1, xlIndirect)
Next i
Now the above code sample does not work of course, as there should be xlAbsolute instead of xlIndirect, but I hope you understand my intention. Do anyone have the answer to my problem? Is there an easy way to implement the INDIRECT function into a big amount of formulas?
View 4 Replies
View Related
Dec 13, 2013
Is there a way I can protect cells so that people have to enter a password if they want to delete the contents.
View 1 Replies
View Related
Aug 5, 2007
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
Exit For
'End If
End If
Next Item
Next i
View 9 Replies
View Related
Mar 4, 2014
I have 1 sheet called Setup which users enter their character name, their class, and their race, additionally the entire workbook manages data for up to 9 individual characters.
On another sheet called C|R|E, I have tasks listed that are based on each class type and each race type that are available. Currently there are nine classes and four races. Currently on this sheet each class and each race has its own individual data lists and such, and formulas are done in a way that user inputted data can be entered anywhere, but wont count unless the class and race match those selected on the Setup sheet. To keep the sheet short in appearance I used command buttons to hide/unhide classes or races not in use by the user.
What I was wondering is (and this could be hard to describe), can data in one column be changed to reflect data based on another column's focus? I will explain in more detail.
Column B contains the task information for each Class and Race. For example Cells B13-B51 for one class, Burglar. Columns D-L is where users enter data to show if the task is completed or not. In this example lets say the users first character listed on the Setup sheet is a Champion. What I want to do is check the Setup sheet Char 1. Then based on what Char 1 is selected as on the Setup Sheet, C|R|E B13-B51 data is changed to reflect it, IF Column D currently has focus. If Column E gets focus then B13-B51 changes based on the class selected on the setup sheet for character 2. and etc.
If this can be done I can remove the 13 different sections and have it down to 2 sections, one for class, one for race. For now I am using check boxes to hide classes and races they are not currently using, but would like to move all classes into one table and all races into one table to they dont have trouble with columns they have to skip over currently.
This is a sample of how I am currently working on the C|R|E sheet, the full code is much much longer since I am using so many command buttons.
View 1 Replies
View Related
May 25, 2009
I have a huge worksheet with 26 columns and 1200 rows. This worksheet is updated by 4 people everyday. Column C, H, N & R are updated daily by these 4 people so these are dynamic columns so to speak. I would like to have a VBA that informs that particular individual when he enters a duplicate data, maybe a popup saying duplicate entry.
View 9 Replies
View Related
Mar 23, 2014
I tried to solve above problem using Data Validation formula. Data validation does not work if somebody copy paste the duplicate values,it works when somebody types duplicate values in next column. So if I want to enter lot of urls to a column then it is completely impossible to type so must have to copy paste from the browser. I have attached a sample sheet, howl to solve this problem either using formula or using Data validation in sample 1 and sample 2 respectively.
View 1 Replies
View Related
Nov 23, 2013
I know how to use data validation to prevent repeat values entered into a column on one worksheet using data validation. I don't understand how to do this across 12 worksheets (months of the year) in a file.
To be more clear: When I enter an ID in a column cell, I want that to be compared to all the other ID's in all the past worksheets and rejected if it has already been entered
All are column D The values entered are identification numbers and have both letters and numbers.
View 5 Replies
View Related
Sep 9, 2013
I have an Excel spreadsheet.
On Sheet1 I have a vast amount of data (over 7000 rows and up to column AA):
eg A Code, B Description, C Page, etc etc AA Proposed Deletions (this column is currently blank and I want to highlight from those found in Sheet2),
On Sheet2 I have a list of proposed deletions (500 rows and 3 columns).
A Code, B Description, C Page
How do I make Sheet1 look up the deletion from Sheet2 and highlight them on Sheet1?
View 2 Replies
View Related
Nov 19, 2013
I have been given a spreadsheet with standard configuration with column headers etc, but when you scroll down the sheet the actual column headers change from the normal A-Z display to be the column headers that have been entered into the cells.
I know this can be achieved similarly by setting Freeze Pane but how this other option has been switched on. I have attached the spreadsheet (which has totally fictitious content).
View 6 Replies
View Related
Feb 22, 2007
When users paste data into cell , I need to protect data validation/ conditional formatting etc. How can one do this?
View 14 Replies
View Related
May 27, 2014
I am trying to secure a worksheet using data validation. I want to prevent users pasting over data validation fields, however I want to allow users to be able to still use the copy/paste feature as long as the data is valid.
Attached is a sample that I found on the forums. The current code does not allow any paste functionality.
Paste Restriction on Validation Cells.xlsm
View 2 Replies
View Related
Aug 3, 2006
I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.
Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.
View 14 Replies
View Related
Nov 13, 2009
I wish to prevent the use of the " ' " apostrophe in an entire column and I was wondering if this can be done with conditional formatting including a message of some type.
View 4 Replies
View Related
Jan 14, 2013
I have a worksheet that you can enter in
Forename, Surname, Area, D.O.B
This has a macro assigned that copies the data and pastes it into another worksheet at the next available row.
Now this works fine, but I want to be able to identify if the data already exists in the other worksheet then prevent it from being pasted and give an error message that the person already exists in the database.
Is this possible?
View 8 Replies
View Related
Aug 6, 2008
[code]...
Above is what I hav already, I was thinking it would help if each card had an additional field added that told you if the contents were valid. This could also say empty if all fields were blank. This field could for example use an if statement to check that you have entered a VS, a start date a project name. It could also check that if you have entered a completion date you also enter review loops. This could be in large red text so it is obvious to someone completing a card that they have not yet filled in all necessary data.
View 9 Replies
View Related
Feb 4, 2010
i have data in a cell that i must view in the sheet and in the print area
but do not want it to print
View 9 Replies
View Related
Aug 20, 2008
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View 5 Replies
View Related
Jan 9, 2009
I currently have a monitoring spreadsheet which my staff enter data into each month. There have been some problems with staff entering data wrong so I have decided to build a userform to prevent errors. I have 2 combo boxes one for selecting a month of the year and another for a particular call the user must enter data for. I want the userform to enter the data from the userform into specific cells in a specific area. e.g. If a member of staff entered data into the userform for February, Call 2 then it would appear in the relevant boxes.
View 5 Replies
View Related
Jun 4, 2007
I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
View 6 Replies
View Related
Sep 18, 2007
i want to import data from sheet2 a1 from sheet1 a1 continously. now problem is when iam importing i shouldnt loose my previous data. eg: at time 11 when i import data to sheet2 a1 from sheet1 a1 with value 9. and at time 12 when i import data to sheet2 a1 from sheet1 a1 with value 10. value 10 will be over wrting value 9. here i shouldnt loose value 9. i want values comming afterwards should come to next row.
View 3 Replies
View Related