Run VBA Code Any Time A Particular Range Of Cells Is Changed
Aug 23, 2002
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.
View 9 Replies
ADVERTISEMENT
Feb 26, 2014
I'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?
View 3 Replies
View Related
Mar 5, 2013
How 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 Related
Oct 28, 2008
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 Related
Apr 15, 2009
I am having a 2000 records of date and time in excel (see the below example). My query is I want to add 6 hrs to each Cell and accordingly the Date should get changed for e.g see the B Column after adding 6 hrs the data should look like this. I want an excel formula (don't want VBA Code) ...
View 9 Replies
View Related
Oct 2, 2008
I have created a userform with multi pages... One of the page is up and running ok.
However I have on one page entries like customer number, name, address, and so on.
I have the same userform from a book which I like to use in one of my pages.
Now what I have problems with is that I need one page to refer to another worksheet of the workbook.
Lets say first page to one worksheet the second page to another. And so on.
Here is the code from the book and it likes to refer to the same worksheet as the userform is opening up.
Private Sub frmDFG_Initialize()
'Startzeile für das Drehfeld festlegen
If .ActiveCell.Row < 3 Then
spn_Change = 3
Else
spn_Change = ActiveCell.Row
End If
End Sub
View 9 Replies
View Related
Jul 24, 2013
In the spreadsheet attached, i have a formula now() which will update the current time in column B if i select opted from column A and similarly in column D the current time will get updated when i select option in column C.
However when i first select the option in column B the current time gets updated but after a while if i select the option in column C the time in the column B is also getting changed.
May be there is a different formula, Not sure what is the trick ?
Time sheet.xlsx
View 7 Replies
View Related
Sep 21, 2009
In my spreadsheet, I want a macro to write date and time everytime the user change a cell with a list validation. On the table, the E column contains the status of each row. I want to know since when the status is that.
View 4 Replies
View Related
Feb 1, 2012
I thought i would create some code to highlight a batch of cells so i can modify formatting all together rather than cell by cell
I am unable to get the generated code to select all ranges together, it only shows the last range
Here is my code
Code:
Sub SelectRanges()
'
' Macro1 Macro
'
'
Range("D12:H12").Select
[Code]....
View 7 Replies
View Related
May 8, 2014
I need to record time next to eg.: W3 cell,
Now I'm using below formula :
=IF(W3<>"",NOW(),"")
but the NOW function keep updating time itself, is there any VBA code or function that I can use with this formula ?
View 4 Replies
View Related
Aug 21, 2013
I have a spreadsheet where an engineer is expected to record sample temperatures of water outlets, along with the time he took the sample. Each outlet has a row on the spreadsheet with a column for the Temperature and column for the time. I would like to automatically input the current time(or time and date) on each line as the temperature is entered.
View 4 Replies
View Related
Aug 18, 2014
I have this VBA code running in cell A1 which gives you a yes/no message box when you try to change the selected cell. I am trying to have the Target.Address = 'Specific Header Cell in Table' but I am not able to develop the code to do so. Here is the code:
[Code] .....
The main reason I have this message box is to prevent the user from changing the cell in the table because the titles in the tables are linked to macros. If they are changed, the macros will not run. Also, I am aware there are codenames for the excel sheets in a workbook. If there are codenames for the header titles in an excel table, I could link my macros to the codenames. As a result, I would not need this macro for the message box.
View 2 Replies
View Related
Feb 27, 2009
I am looking to have a macro which will automatically add the username and date to a cell, when the cell above changes (or in this case =x). I have a bit of code from a previous project, but I have changed Target.Row to Target.Column and it is not behaving as I require:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2:BZ2"), Target) Is Nothing Then
Range("C" & Target.Column).Value = Format(Date, "dd-mmm") & " " & Format(Time, "hh:mm") & " by " & (Application.UserName)
End If
End Sub
See the attachment for the example. What I need is whenever row 3 ="x" I need the corresponding cell in row "d" to update with the latest date, time and username. Please not the macro is currently not active on the sheet as i've added the ' character.
View 3 Replies
View Related
Jan 21, 2014
I wrote the following basic code earlier (which will end up being part of a larger code)
Code:
Dim DateInput As String
Dim LastCell As String
Sub LastRowInputBox()
LastCell = InputBox("What is the last row number in the range?", "Last Row Input")
[Code] ......
As you can see, all the ColumnCopyInsert Subroutine does is copy select/copy four columns (E:H), inserts the copied selection immediately to the right, inserts formulas and autofills down to the last row required. I added the LastRowInputBox routine as the range of rows varies from week to week (inserting new rows, deleting others).
The code works great...up to a point unfortunately. If ran as it is, everything that should be copied/pasted will be and cells autofilled to the row specified via the InputBox. Here comes the problem, if any rows are either inserted or deleted then the code throws out "The object invoked has disconnected from its clients" when it reaches the first instance of the following line:
Code:
Selection.Insert Shift:=xlToRight
At which point, Excel hangs (or maybe in some sort of loop) and I have to open Device Manager and close the Excel exe process.
View 5 Replies
View Related
Apr 15, 2008
I used the code below for Conditional Formatting. This works fine but the VBA-code crashes when I delete more than one selected cell. Is there a simple modification possible to prevent this from happening?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:C250")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 3 Replies
View Related
Jul 16, 2014
I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
View 7 Replies
View Related
Jun 10, 2009
I asked for a macro to delete the whole row if a duplicate customer number was found in column B. Sometimes, though, my column numbers change. So, logically thinking, I simply changed the criteria, but the macro ONLY seems to work if duplicate customer numbers are in column B only.
This code below won't work if the Customer Number is in column D instead of B even if the reference of B:B is changed to D:D, it doesn't carry the macro over.?
View 3 Replies
View Related
Feb 19, 2010
I've been trying to copy values from one range to another, and the ranges need to be dynamic. Normally I use a combination of the 'range' and 'cells' properties, like this:
View 2 Replies
View Related
Jan 31, 2014
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 .
View 1 Replies
View Related
Mar 22, 2009
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
View 4 Replies
View Related
Mar 11, 2014
I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
View 11 Replies
View Related
Jan 6, 2014
When I move the range rDes, another range rOrig with the same address gets changed. How can I prevent this?
Code:
Sub Macro1()
Dim rSrc As Range
Dim rDes As Range
Dim rOrig As Range
Set rOrig = Selection
[Code] ........
View 3 Replies
View Related
Mar 14, 2008
When I input time in the h:mm:ss format it add a am/pm to it. I tried formating it to remove it yet it wont go away. What I am trying to do is add up time in the G Column but all I get is 0:00:00.. I added the [h]:mm:ss format to that cell... Does this have to do with Execl adding am/pm to the time? If it is how do I stop it.
View 3 Replies
View Related
Feb 11, 2014
I am trying to have one cell (G5) populate with a timestamp when a change is made to any cells in a range (some are merged). The code below worked for a second, until it didn't.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F8:G33")) Is Nothing Then Exit Sub
Range("G5").Value = Now()
End Sub
*Getting a yellow arrow by the row starting with Range("G5")
View 6 Replies
View Related
May 3, 2007
Is it possible to always print out a specific pivot table even if changes/selection and range have changed? What I have right now is a button that prints out the pivot table (assuming the range is not changing). But if a user changes selection the button prints only a part of the pivot table.
View 3 Replies
View Related
Apr 4, 2008
trying to add cells together that contain time (hours and minutes). The cell containing the total formula shows the #VALUE! result when and only when one or more cells being added do not have a value in them.
The times have been entered as actual times of the day, formatted for AM/PM.
A simple adding formula works fine, but all cells must have a value in them to obtain a valid result.
I attach a sample of the sheet.
View 3 Replies
View Related
Jan 23, 2014
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 Related
May 21, 2008
how 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:
View 9 Replies
View Related
Jul 25, 2014
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.
View 9 Replies
View Related
Feb 22, 2008
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.
View 9 Replies
View Related
