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?
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.
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:
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?
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.
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 RelatedI 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?
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
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")))
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?
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
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 RelatedThis 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 i have a challenge i need to prevent the value is more than A1 cell value in B1.
Example: if A1 value is 10 then unable to enter morethan 10 in B1 cell.
Am trying to create a simple spreadsheet with some multiplication functions on it. Using this example:
Cell A1 = 5 (multiplicand) x
Cell A2 = 10 (multiplier) =
Cell A3 = 50 (product)
All is well providing I have a number value in cells A1 and A2. When I don't, I would like them to remain blank, as well as A3, the product. The problem I face is when I remove the values in either A1 or A2, what's displayed in A3 turns to the word - #VALUE!. Which also shows up on the printed page.
My question is, like removing the checkmark for "zero values" under tools and options to prevent the "0"'s from being displayed, is there a way of preventing the word "#VALUE!" from being displayed when the values for the multiplicand and/or multiplier have been removed?
How can you prevent a cell which contains a validation list from being dragged?
View 9 Replies View RelatedI have a worksheet with multiple entry boxes in a row. Depending on the value in one row, another row will either yield the result from a formula, or remain blank, and this cell needs to be locked. I was able to do this by first putting the formula into the destination cell, and then using custom data validation that says the cell value = "". So this prevents anyone from typing in that cell, no matter if it yields the formula result or must stay blank. What it doesn't protect against is someone deleting the contents of the cell. The reason I don't want to protect the cell is because I want people to be able to highlight the whole row past that cell and delete it all at once. So either using Data Validation, a formula, or VB programming I need to not allow someone to do ANYTHING to that cell, even delete the contents, without locking the cell. Hopefully it can be done, thanks!
For example, if E17 = "X", then K17 = INPUT!$M$28*J17. If E17 <> "X" then K17 is blank. Either way, I don't want any typing or deleting of K17. This applies for range K17:K91, and corresponding E17:E91.
If A1 = "Yes"
Then don't allow input into cells B1 and C1. Or delete anything in B1 and C1 perhaps?
Cell A1 would be the result of a formula
How would I go about creating something like the above?
how can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1
I want to prevent an entry in these cell and if you try an error message will display
How can I stop the overlapping cells?
View 2 Replies View RelatedIs there any neat little way of preventing a character say '=' from been entered in a cell, kinda similar to the validation but for a 'dont allow' function.
View 14 Replies View RelatedI have one main tab which I pull data from other various tabs. To match data the data, I create a "common unique ID", in other words I create a column on each tab and connect various cells with the ampersand.
So in each of the various tabs (and the main tab), I'll have a column with something like =A2&B2&C3
If the "unique ID" matches between the tabs it will pull back the desired phrase, or it will be left blank. (Just using an =IF(ISNA) formula)
If you have followed so far, on my main tab, I have a column for each of the various tabs. The information I am trying to match could be on any of the tabs, so I need a column using an =IF(ISNA) formula to attempt to match each tab. So, on the main tab, there are several columns which might have the phrase I am looking for. I use another ampersand function to add those columns. My problem is that occaisionally, what I am looking for is on multiple tabs. So, if I am normally pulling the phrase "Pending Loans Report" when a match is made, if it is on two tabs, the cell will add it twice "Pending Loans ReportPending Loans Report"
I realize this is confusing, so I attached a spreadsheet. The "main" tab I was referring to is sheet1. I really simplified it down to post here, but I think the main idea of my problem is still evident. The problem is line 15. Note how column M has "Pending Loans ReportPending Loans Report" - I'd like it to only say this phrase once. The macro pastes special values at the end, but I've highlighted some cells in red which show the formulas I used for that column. Columns N, O, P, Q, etc. all have virtually the same formula.
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 Relatedi have data in a cell that i must view in the sheet and in the print area
but do not want it to print
I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?
View 5 Replies View RelatedI would like a macro which disables the close function of a worksheet if a particular cell or has not being filled out?
View 3 Replies View Relatedsee the attached "sample.xls". In cell "D1", after I typed in some text and pressed enter, the cell format changed. It only happened for texts not for numbers. There's no macro or conditional format that I could find.
View 2 Replies View RelatedI 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 RelatedUsing 2003 - Is it possible to prevent cell data entry automatically creating a hyperlink for email/web url's. Using
View 4 Replies View RelatedI have one issue with my protected worksheet. Some locked cell contents are linked to cell contents located in another sheet. When I double clicked on these cells, a warning window poped up but also opened the sheet in which the corresponding cell is located. This is quite annoying to have to go back to the former sheet. How can I prevent it ?
View 3 Replies View Related