Macro To Prevent Changing The Value Within A Cell
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
ADVERTISEMENT
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
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
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
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
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
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
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
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
Apr 29, 2008
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.
View 9 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
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
Aug 3, 2012
I have unprotected cells in excel where I need to change the content (number) and cell color, but I don't want the cell borders to change. I'm a beginner with vba. I've tried protect sheet and workbook options, but looks like I need a vba code and don't know where to start.
Win 7
Office 2007
View 4 Replies
View Related
Jan 12, 2009
I did a search on this site and found some code I was looking for (see link:
http://www.excelforum.com/excel-prog...e-in-cell.html - Leith Ross's response code).
The code works perfectly, however, if I save the workbook with a different spreadsheet on top than the spreadsheet referred to in this code, I get an error message: Method 'Range' of object '_Worksheet' failed.
I should state that I did change "Private Sub Workbook_Open()" to "Private Sub Auto_Open()".
I assume I need to modify the code but am unsure as to how.
View 11 Replies
View Related
Sep 11, 2008
Is there a way to change the value of cell A1, for example, from one value to another, hit the enter key, and activate a macro this way.
A couple of qualifications:
- the value in cell A1 will never be the same (that is it could be -23.43 or .12)
- I can not simply tie the macro to the "enter" key as there will be numerous cells (A1, B3, C90 and so on) in the same sheet that I will need to do this with, each tied to a different macro.
View 14 Replies
View Related
Jan 24, 2008
I am writing/creating macros and I want to create one that will automatically run when a cell value is changed to a certain entry.
View 2 Replies
View Related
Mar 27, 2009
Made a macro to Copy a value and paste is as "Special" for Value only.
The problem is it won't function correctly Unless I force a switch to the sheet where the calculations are. (Which I want to avoid)
The current code is:
View 5 Replies
View Related
Jan 5, 2009
I have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list
Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount
I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.
For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)
View 9 Replies
View Related
Jul 21, 2014
When a change is made in cell L11, cell N11 should be locked automatically for typing. This is true for the cells between L11:L25, and cells N11:N25.
I have been trying to come up with a code that will:
1 - start my macro when f.ex. L11 is > 0
2 - lock N11 when L11 is filled out
3 - unlock N11 when the input in L11 is deleted
This is my attempt so far to put together a code:
[Code] .....
I'm using Excel 2010.
View 1 Replies
View Related
Mar 4, 2012
I have a Macro 'Timenow' to give current time in a Cell of MS Excel, but then it changes all Cell values of sheet where the macro was used.
Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub
Even assigning value to variable does not work:
Sub Timenow()
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
Dim TN As String
TN = Format("=now()", "h:mm")
ActiveCell.Value = TN
End Sub
how to restrict the macro to change the value of current/active cell only without effecting other cell values?
View 4 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Mar 14, 2007
Prevent saving and allow only through Macro
I am contracting an excel template and need to stop users saving the file using “Save” or “Save As”. I am able to do this by using the private sub below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
I also understand that by switching to “Designer” mode you can save the file and once re opened designer mode will be switched off. What I need is to allow saving using a macro, is there a code that I can insert in the macro to switch designer mode?
View 9 Replies
View Related
Feb 21, 2002
Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??
View 9 Replies
View Related
Aug 8, 2007
I have a macro, that when running needs to display certain sheets that should be hidden again when the macro is done. I solve this by using the code
Sheets("Sheet1").Visible = True
'macro code
Sheets("Sheet1").Visible = xlVeryHidden
But what if the macro fails? Or if the user ends it by pressing Esc? Then he will be able to see the hidden sheet. Is there something I can write in the code that makes the macro hide the sheet, then show the error message?
View 5 Replies
View Related
Nov 14, 2008
I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.
View 6 Replies
View Related
Feb 9, 2009
Is there a way to prevent access to the code of a macro? I am interested in sending out some macro's, but would prefer that the user only be able to run them, and not be able to access the code via the edit button. Curiously, there have been occasion's where I wanted to edit my own work, but the edit button did not function, so I had to exit and start over to proceed. From this I'm guessing there must be a way.
View 2 Replies
View Related
Aug 6, 2008
I have an add-in that I'm passing around to my users, and though it has 8 or 9 subs, it only has 2 that they need to see. However, I'd rather not relegate all of the code to a single module, as there is quite a bit of it. I'm aware that I could change all the subs to functions, but they would still appear in the UDF list, and this would also be confusing to the individual responsible for the upkeep of these macros (and I'd have to rework the code a little bit).
I'm just asking if there is any direct way to do this, before I go to the trouble of making a lesser workaround. Google seems to think no, but "don't show macros in list" isn't a very solid search.
View 9 Replies
View Related
Aug 5, 2009
I'm using the following code to delete columns:
Dim rng As Range
Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
rng.Cells(i).EntireColumn.Delete
Else
i = i + 1
End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
View 4 Replies
View Related
Nov 23, 2009
I'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
View 2 Replies
View Related