I'm after some vb code that will only allow the range - PRODUCT_Selections!A2:C370 to calculate, when something is changed in the range - Input!T11:V500
I would like all other calculations in the workbook to continue as normal.
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).
Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.
I really do not think the actual formulas matters at this time...
Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
There is a column in my spreadsheet which needs to reflect a " percent done".
I used to enter just a numerical value in (eg. 20) to reflect 20% done. My boss wanted it to show as an actual percentage (eg. 20%). I changed the format to Percent, Zero decimal places. Then you had to enter .20 to get the 20% to show.
Now my boss doesn't like that - and wants to be able to enter "20" to get the 20% to show. I put the following code in my SelectionChange event...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'other logic
If Target.Column = Asc(PercentDoneColumn) - 64 Then
If Target.Value <> "" Then
Target.Value = Target.Value / 100
End If
End If
'end other logic
End Sub
My problem is that this works well when putting a new percentage in, but not for when you click on a cell that already has a percentage in it. For example, if you have .2 (displaying "20%" in the cell), and click on that cell, it divides that number by 100, and gives you 0.2%, displaying as "0%". I want to enter a number between 0 and 100, have it displayed and stored as a percent. At this point, I'd settle for the displayed, but would prefer to have it stored as a percent, too.
I also want it to not reset my value unless I'm putting a new value in!
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
On an excel document that was originally formatted as a number and then locked, somehow users have changed the formatting to a date. I think it is an accident, but I can't figure out how they are doing it. I have several data files on a server where several offices have access to them to input data and somehow formatting gets changed sometimes, even when locked.
View 3 Replies View Relatedhow to alter this Private Sub for my code to execute automatically once all 3 cells have changed. At the moment it executes as soon as any of the 3 cells change.
Here's the Private and Public Sub codes:
Column-A starts out empty.A user changes cell B5 and navigates to another cell (whether by clicking, tabbing, or enter-key, it doesn't matter).An "X" is placed in Column-A for that row.Preferably the user would then be taken to the cell they were navigating to (whether by mouse-click, tabbing, or the enter-key).This happens every time a change is made to a cell.I have no idea how to code for this.
I'm not sure what constraints I might have for exceptions. For instance, can it handle multiple row being pasted into or cleared at once, etc. ( Deleting columns will not be allowed in this instance.)
------------------------------------
ADDENDUM:
I continued my search and found the instructions at [url]
My ultimate goal in putting an "X" in Column-A is to identify rows with changes, so they can then be copied into an "archive" worksheet. I'm thinking this VBA version of track changes would be much better than what I was earlier thinking.
I have a workbook with two worksheets, Data and Archives. I want to let a user make changes to data in the "Data" worksheet. Then when they save or press a button all rows with edits will be copied into the "Archives" worksheet.
Appending date/time and username stamps in the trailing columns would be very handy too.
I have sheet where percent change is calculated. All of the data isn't put in at once, so i don't want it to a calculate the percent change until both cells contain a number.
Here is the formula i am using: =IFERROR((D4-B4)/B4,"")
Right now when i fill in the first value it automatically returns a percent change of -100%, i want it to wait until both cells are filled with data.
Is there a way to set 2 cells equal, while allowing either one to be manually changed? So if I put in value of 10 for Cell1, Cell1 and and Cell2 will both be equal to 10. Then if I change Cell2 to a value of 20, both cells will be equal to 20.
View 5 Replies View RelatedHow do I protect a select range of cells from being changed? There may be some formulas in certain cells, which produce data based on other worksheet cells. These cells are not for user data input, but for data output only. I don't want end users to acidently delete the formulas in cetain range of cells, by entering a value in the cell, which then will write over the formula.
View 5 Replies View RelatedI've managed to piece together a VBA code that works perfectly for my needs. The problem is that the sheet it is on gets adjusted a lot, and I only need the code to run when certain cells are changed (rather than each time any cell is changed).
Here is the code I have now:
[Code] .....
Put simply, I need this code to run only when a cell in Range "TValueDeal" is changed. Is making this adjustment as simple as adding a couple lines of code, or is there a better way to write the entire thing?
Colouring calendar entries based on a date match... VBA to colour a cell on each line based on date match
The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.
Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone
[Code] ......
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?
I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.
1. Is there a way to call VB (say, sheet1.myFunction())?
2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)
The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
I'm trying to calculate whether a time value located in a cell is between the times in two other cells. Here is what I tried:
=IF((AND($A62>=C$55,$A62
If the value of any cell in in column D is changed to "Closed", a message box will appear that says "Others must be notified when item is closed". In that message box, I want two buttons: Button 1 One is cancel and will undo the change. Button 2 will open MS Outlook with pre-filled email addresses and will attach the open worksheet. After the email is sent, to return to Excel.
View 4 Replies View RelatedI have the following code on my worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Do_it
End If
End Sub
Cell B2 contains an employee number. What I need to do is save some data for the employee number that was in cell B2 just before it was changed.
Example: If employee number "10" is entered in B2.
Then the user were to change the employee number in cell B2 to "55" I would like to use the previous value "10" to do some VBA stuff with.
I'm working on a timetable for my boss and i'm trying figure out how to have vba detect when a specific set of cells gets an x typed into it and calculate 1 cell = 36.50 hours and input into the appropriate Sum column. The Sum Total is Column AR and i've put x's into some of the cells that will be containing them, i'm sure if i just get the basic code needed i will be able to implement it.
View 5 Replies View RelatedI have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
View 1 Replies View RelatedI have the following range of cells B2:Y3700. I want that when a value from 1 to 9999 is inserted in one cell of the above mentioned range, to copy the row number of that cell in the cell that is located in column Z and row is the number inserted in that cell. For example, if the number "1234" is inserted in one cell from range B2:Y3700, to enter in cell Z1234 the number of the row where is located the respective cell. At same the time is very important to me to not permit to insert the same number in B2:Y3700, so i believe that is need a macro code to check the cell from column z to see if value already exist .
View 5 Replies View RelatedIs it possible to save a workbook whenever any changes are made to any cell in a specific column? The reason - I'm putting an excel workbook out on a network drive for 4-5people to share. The sheet contains work items which are imported every 4 hours... Column A contains the status.
I want to save the file every time anybody changes a status to prevent multiple users from working on the same item. I realize that I could build a form then load each workitem into the form, then apply the save to a change event on the field/control, but that's something for next week - I need to get this out the door, bare bones, as soon as possible.
I am looking to insert a date in a Cell when another Cell's number has been changed.
I was using If statement as: If(isnumber(f4),TODAY())
but I don't think it is working properly
the cells columns would be, e.g. "Consumables stock" and "date updated".
So if someone changes a number is the "consumables stock" column, date updated column auto-updates with current date. How would I go by in doing this?
Is it possible to change hyperlink as per the cell reference changed in a cell. for example :- in cell D2 the apply a match formula to find out a cell reference (eg "A"& Match function based on info type on cell A1) Now I want to create hyperlink as per the cell reference mentioned in cell D2. suppose I type Red in A1 and D2 give me the cell reference A51 than automatically Hyperlink create for A51. and it continiously changed whatever i type in A1 and what cell reference is showing in D2.
Sheet1ABCD1Type Abbrivation12Full FormACell AddressA13Is it possible that I click on Cell D2 and it goes to particular cell 45Abbrivation6Short CodeDescription71A82B93C104D115E126F137G148H159I1610J1711K1812L1913M2014N2115O2216P2317QExcel 2007Worksheet FormulasCellFormulaB2=VLOOKUP(B1,A6:$B$1000,2,0)D2="A"&MATCH(B1,$A$7:$A$1000,0)
which will calculate value of a cell when one of the variables has been changed?
To illustrate what I have in mind, an ecxample:
* User can add values to cells using UserForm
- Component name (to Cell "A1")
- Component price (to Cell "B1")
- Component quantity (to Cell "C1")
[Code]....
When a cell changes i would like the cell in the next column to record the date of this change. After achieving this i want to set up conditional formatting to compare this date to the current date and if they are the same then change the original cell's colour to show it changed today!
I started using the code below (from ozgrid) which was working fine BUT my cell value in column A is actually taken from a different column. when the different column cell value changes my cell in A changes but the script below does not seem to see this as a change. it only records the date if i physcially change the cell in A rather than another cell prompting it to change.
based on my overall objective and my plans to date please can someone suggest a solution?
current code below....
The following code works fine but now I want it to work without a userform. SO there is no userform and so no controls.
When the user changes date in a cell then a macro should run and following kind of code should execute.
[Code] .......
I have a spreadsheet where sheet("summary").Cells("K4") = sheet("component").Cells("G7").
This is written as a formula, e.g. =SUM('Component'!G7).
I am trying to achieve the same through VBA. I can get the summary cell to copy the others. but it just copies it the once when it is created. How can I make this re-calculate everytime it is changed?
I know that =now() will give me the time, and keep updating itself, but is there a formula I can use that will give me the time the cell was written into and maintain that time .. example, if I entered something into the cell at 3:45:21pm I want that time recorded in that cell and not change as they day moves on
View 9 Replies View RelatedI want to capture the date and time that a cell is changed.
So if the value in cell A2 is changed, then cell B2 date stamps with the current time; similiarly if cell A3 is changed, then cell B3 date stamps...
What i am trying to do:
1) check whether any value in column J, rows 7-18 is changed,
2) check whether the value is above the value in col H, respective row multiplied by 1,25,
3) if point 3 true, clear the contents of cells E24, F24, and show up a messagebox,
4) run another macro by pressing button in cell C24 in order to fill the E24 and F24 cells again.
What happens: Points 1-3 work perfectly well, but then i have a problem in point 4 - the messagebox of point 3 appears again and the work of macro of point 4 interrupts showing an "Application-defined or object-defined error".
The file with the code is attached : Example.xlsm